Hiding Rows

jlkirk
2StarLounger
Posts: 168
Joined: 25 Apr 2010, 14:12

Hiding Rows

Post by jlkirk »

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.
You do not have the required permissions to view the files attached to this post.

User avatar
HansV
Administrator
Posts: 78237
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Hiding Rows

Post by HansV »

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

jlkirk
2StarLounger
Posts: 168
Joined: 25 Apr 2010, 14:12

Re: Hiding Rows

Post by jlkirk »

Looks like it does. Thanks as usual, Hans!