I posted this on the Excel Help Board and Woody's as well, but couldn't get a satisfactory resolution. Please see the attached Excel file. Range B6:E11 on Sheet1 contains cells that contain formulas that link to Sheet2. What I would like to do is hide a row where all of the entries from the formulas in the cells of that row in the range B6:E11are returning a zero from Sheet2. Any help? I have highlighted in yellow the rows I would like to hide.
Thanks in advance.
Hiding Rows
-
- 2StarLounger
- Posts: 168
- Joined: 25 Apr 2010, 14:12
Hiding Rows
You do not have the required permissions to view the files attached to this post.
-
- Administrator
- Posts: 78237
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Hiding Rows
Does this do what you want?
Code: Select all
Sub HideRows()
Dim r As Long ' row number
Dim m As Long ' last used row
Dim c As Long ' column number
m = Cells(Rows.Count, 1).End(xlUp).Row
' From row 6 to the last filled row
For r = 6 To m
For c = 2 To 5
If Cells(r, c) <> 0 Then Exit For
Next c
If c > 5 Then
Cells(r, 1).EntireRow.Hidden = True
End If
Next r
End Sub
Best wishes,
Hans
Hans
-
- 2StarLounger
- Posts: 168
- Joined: 25 Apr 2010, 14:12
Re: Hiding Rows
Looks like it does. Thanks as usual, Hans!