Easy (I think) VBA reference Question

[sbeatton]

I want to use an if statement as below however I want it rather than look for 0 to look for blank cells, how do I reference it. Using "" does not seem to work, nor isempty(). Full code is

Sub aHideBlanks()
Dim c As Range
For Each c In Range("D12:D16").End(xlDown)
If c = 0 And c.Offset(, 3) = 0 Then c.EntireRow.Hidden = True
Next c
End Sub

Thanks,

 

[SIXTH SENSE]

hi!

Try if this works!

Sub aHideBlanks()
Dim c As Range
For Each c In Range("D12:D16")
If c = "" Then c.EntireRow.Hidden = True
Next c
End Sub

 

[sbeatton]

Have tried "" previously but does not seem to want to work. Cells definitely have no value in them.

Any other ideas?

 

[SIXTH SENSE]

your loop does not loop in all the cell in range.
have you tried the code?
it works for me when the cell is empty.

 

[sbeatton]

Maybe I have made a mistake with my range. I am trying to check the array D12:G16 and if an entire row (eg. D12:G12) is blank hide that row. That is why I have said if c = D12:D16 AND the offset (3 columns to right) is blank then hide the row. Any suggestions.

 

[sbeatton]

Problem was with the offset command. Needed to rewrite as follows

For Each c In Range("D12:D16")
If (c = "" And c.Offset(, 1) = "" And c.Offset(, 2) = "" And c.Offset(, 3) = "") Then c.EntireRow.Hidden = True
Next c

Wanted to check four columns and not just one. Is there any simpler way to write out of interest?

 

[SIXTH SENSE]

hope this helps!

Sub hideCompRow()
Dim Myrange As Range
Dim Mycell As Range
Set Myrange = Range("D12:D16")
NumColToCeck = 4 'from column D to G
For Each Mycell In Myrange
    If Application.WorksheetFunction.CountA(Range(Mycell.Address & ":" & Mycell.Offset(0, NumColToCeck).Address)) = 0 Then Mycell.EntireRow.Hidden = True
Next Mycell

End Sub