Hide row if particular cells are empty

[Francis Hookham]

Text can be in none, any or all of the cells of columns 4, 5, 6 and 7 in
each row.



I need to hide the row only if all cells 4, 5, 6 and 7 are empty but leave
the row visible if any cell includes text.



I just cannot see how to do it - everything else is ok - determining the
number of rows which vary and progressing from row to row - it is just the
test in each row I need help with.



Best wishes for 2007 - another hour to go here in the UK.



Francis Hookham

 

[Tom Ogilvy]

Assuming the cells are actually blank and NOT just look blank with a formula
of type =if(true,"","abc")

Sub HideRows()
Dim rng As Range, rng1 As Range
Dim ar As Range. rw as Range
Rows.Hidden = False
Set rng = Intersect(ActiveSheet.UsedRange, ActiveSheet.Columns(4).Resize(,
4))
Set rng1 = rng.SpecialCells(xlBlanks)
For Each ar In rng1.Areas
For Each rw In ar.Rows
If rw.Cells.Count = 4 Then
rw.EntireRow.Hidden = True
End If
Next
Next
End Sub

 

[Guest]

Hi Francis,

Try this little macro.

Option Explicit

Sub hiderowson4567()

Dim lLastRow As Long, lRow As Long

With ActiveSheet

lLastRow = .UsedRange.Row + .UsedRange.Rows.Count - 1
For lRow = 1 To lLastRow
If IsEmpty(.Cells(lRow, 4)) Then
If IsEmpty(.Cells(lRow, 5)) Then
If IsEmpty(.Cells(lRow, 6)) Then
If IsEmpty(.Cells(lRow, 7)) Then
.Rows(lRow).Hidden = True
End If
End If
End If
End If
Next lRow

End With
End Sub

 

[Francis Hookham]

Many thanks Martin and Tom - I have used Martn's solution because I
understand the construction whereas Tom's is a bit beyond my simple VBA
abilities.

My rate for this reply?

It is the same as always - I am amazed at the speed and quality of the
replies and very grateful for your dedication to helping us.

Francis Hookham