Hide row if particular cells are empty
 

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

Hide row if particular cells are empty
 

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


--
Regards,
Tom Ogilvy




"Francis Hookham" wrote in message
...
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

Hide row if particular cells are empty
 

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

--
Hope this helps
Martin Fishlock, Bangkok, Thailand
Please do not forget to rate this reply.


"Francis Hookham" wrote:

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

Hide row if particular cells are empty
 

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


"Martin Fishlock" wrote in message
...
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

--
Hope this helps
Martin Fishlock, Bangkok, Thailand
Please do not forget to rate this reply.


"Francis Hookham" wrote:

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