Hide empty rows

[wafs]

I need to add an if statement to a macro. I need it to look for empty cell and hide the row. I created the following, but it doesn't work. What am I missing?

If sheets(destsh).Range(reportlocation) = null then
sheets(destsh).Range(reportlocation).hide

 

[SkipVought]

hi,

If sheets(destsh).Range(reportlocation).value = "" then
sheets(destsh).Range(reportlocation).entirerow.hidden=true

Skip,

_{[red]Be Advised![/red] A chicken, who would drag a wagon across the road for 2 cents, is…
POULTRY in motion to PULLET for a PALTRY amount!}

 

[lgclims]

If sheets(destsh).Range(reportlocation) = null then
Rows("r:r").EntireRow.Hidden = True
end if

where r = current row

 

[wafs]

Thanks guys, I will give them a try and let you know how it turns out.

 

[SkipVought]

BTW,

NULL has a specific meaning in VB. Look it up!

Skip,

_{[red]Be Advised![/red] A chicken, who would drag a wagon across the road for 2 cents, is…
POULTRY in motion to PULLET for a PALTRY amount!}

 

[wafs]

I null means empty. I tried both suggested coding and none of them worked. Did I put the code in the wrong spot? Should I have created a new function?

Sub update_headers()

'====================================================================
' UPDATE HEADER SHEETS
'
'Description: Automatically updates header sheets with header you request
'
'Macro Created on 3/16/2006
'
'Written by Wendy Smith
'=========================================================================

Dim cellstart As Integer 'starting point of informaiton (row)
Dim p As Integer
Dim r As Integer
Dim x As Integer
Dim n As String 'counting variable
Dim TestRange As String 'Test String
Dim TestRange2 As String 'Test String
Dim TestRange3 As String 'Test String
Dim Output(1 To 10) As String
Dim m As String
Dim sh As String
Dim destsh As String
Dim reportlocation As String
Dim header As String 'header you want to run Holds header

cellstart = 3 'Starting point of source sheet
p = 0
header = InputBox("Which header do you want to run?", "header") 'input the requested header

TestRange = header
sh = "Daily" 'Work sheet where the header information is kept
destsh = header ' desination sheet for header

n = cellstart
For x = 1 To 500
TestRange2 = "B" + n 'column where to match header request
TestRange3 = "A" + n
If UCase(Trim(TestRange)) = UCase(Trim(Sheets(sh).Range(TestRange2))) Then
p = p + 1
m = p + 3 'Tells where to start importing information on dest sheet
reportlocation = "A" + m 'starting point of output file
Sheets(sh).Range(TestRange3).Copy Sheets(destsh).Range(reportlocation)
n = n + 1
TestRange2 = "B" + n
TestRange3 = "A" + n
Else
n = n + 1
TestRange2 = "B" + n
TestRange3 = "A" + n
'If Sheets(destsh).Range(reportlocation).Value = "" Then
'Sheets(destsh).Range(reportlocation).EntireRow.Hidden = True
'End If
If Sheets(destsh).Range(reportlocation) = Null Then
Rows("r:r").EntireRow.Hidden = True
End If
End If
Next
End Sub

 

[SkipVought]

Wendy,

YOU are in the best place to debug your code.

Use BREAK. Step thru the code. Use the Watch window to check values.

No one can do this for you.


Skip,

_{[red]Be Advised![/red] A chicken, who would drag a wagon across the road for 2 cents, is…
POULTRY in motion to PULLET for a PALTRY amount!}

 

[Loomah]

Wendy
A couple of things:-
As already stated don't use Null for this!

Rows("r:r"). - this doesn't really mean anything
You have declared a variable r but it doesn't seem to be initiated with a value anywhere. But the code you have used suggests you want to hide the row with the heading "R" which, because rows are numbered (not lettered) doesn't exist. Unless I've missed it somewhere, assign your value to r and use
Rows(r).Hidden = True


;-)

If a man says something and there are no women there to hear him, is he still wrong?
How do I get the best answers?​

 

[wafs]

Sorry, I copied it into the code to look at and I did not take it out after I was done, but your code did help me on getting something set up to hide the rows.