[Solved] Conditional Formatting : Hiding Rows containing Zer

  • I'm building a consolidated report on a shared drive which links to several other workbooks. My problem is this: Because there are so many rows which return zero values from the linked cells, the report is far too long. I can't remove the rows, because at some point, the linked cells might have values... so I need to hide them somehow. Is there a way to automatically hide rows which contain (all or some) zero (or prespecified) values?

  • Have you tried AutoFilter or an Advanced Filter?

    <a href="http://www.mrexcel.com/relayforlife.shtml" target="new"><img src="http://www.myimgs.com/data/vonpookie/anne_relay.gif"></a>

  • I use this macro attached to a button. You could use it in a Before Print macro.

    I use a range named HideRange for the values to be checked.


    Sub HideZeroLines()

    Dim c
    Application.ScreenUpdating = False

    With Worksheets("report").Range("hiderange")
    .EntireRow.Hidden = False
    End With

    For Each c In Worksheets("report").Range("hiderange")
    If c.Value = 0 Then

    c.EntireRow.Hidden = True
    End If
    Next c

    ActiveSheet.DisplayAutomaticPageBreaks = False
    Application.ScreenUpdating = True

    End Sub

    There are three types of people in this world.
    Those who can count and those who can't.

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!