Macro to hide rows with no data

Posted by Dwight on June 07, 2001 1:47 PM

This is a variation on a problem Gerd was kind enough to solve but I can't figure how to make it work with rows instead of columns. Want a macro to hide rows within which no data is entered for the range A40:O60. Any help will be appreciated.

Posted by Jose on June 07, 2001 2:05 PM

Sub HideRows()
Dim rw As Range
For Each rw In Rows("40:60")
If Application.Count(Range(Cells(rw.Row, 1), Cells(rw.Row, 15))) = 0 Then
rw.Hidden = True
End If
Next
End Sub

Sub UnhideRows()
Rows("40:60").Hidden = False
End Sub

Posted by Dwight on June 08, 2001 7:09 AM

One little glitch, Jose

Works well except that sometimes I have text in column A (was assuming that "data" could be text or numbers) and nothing in B through N. The macro seems to hide the row in this case when I want to keep it unhidden. Is there a fix?

Posted by Barrie Davidson on June 08, 2001 7:41 AM

Re: One little glitch, Jose

Replace:
If Application.Count(Range(Cells(rw.Row, 1), Cells(rw.Row, 15))) = 0 Then
With
If Application.CountA(Range(Cells(rw.Row, 1), Cells(rw.Row, 15))) = 0 Then

Regards,
Barrie

Posted by Dwight on June 08, 2001 11:17 AM

Beautiful! Thanks, Barrie