EXCEL VBA not unhiding

Zipbang
Zipbang used Ask the Experts™
on
It's probably b/c I am tired, but I can't figure out why this piece of VBA code will not unhide the cells.

The cells in question are calculation cells (if,then) that are either a numeric value or nothing ("").  When the cell is nothing ("") they should be hidden, otherwise they should be unhidden.


Sub HideEndorsementRows()

    Dim rRow             As Range
    Dim rRowRange        As Range
    Dim rCell            As Range
    Dim bHide            As Boolean
    
    Set rRowRange = Intersect(ActiveCell, Range("A1:A17"))

    ActiveWindow.DisplayZeros = False
    Application.ScreenUpdating = False

    For Each rRow In rRowRange.Rows
        bHide = False

        For Each rCell In rRow.Cells

            If Len(rCell.Value) = 0 Then 'check here if cell has a value other than ""
                bHide = True
                Exit For
            End If
        Next rCell
        rRow.EntireRow.Hidden = bHide

    Next rRow

    Application.ScreenUpdating = True

End Sub

Open in new window

Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Top Expert 2010

Commented:
As sample file would be helpful :)
Mechanical Engineer
Most Valuable Expert 2013
Top Expert 2013
Commented:
Your code appears to work for me. It appears to hide the row containing the selected cell if it is blank. If more than one cell is selected, only the top left cell is considered. A cell containing a value of 0 will appear like a blank, but will not be hidden. A cell containing an empty string "" (such as from the return value of an IF function) will be hidden.

I did make one small change to it: test whether the active cell was in the range A1:A17,. If not, there was no point doing anything else.

Sub HideEndorsementRows()

    Dim rRow             As Range
    Dim rRowRange        As Range
    Dim rCell            As Range
    Dim bHide            As Boolean
    
    Set rRowRange = Intersect(ActiveCell, Range("A1:A17"))
    If rRowRange Is Nothing Then Exit Sub

    ActiveWindow.DisplayZeros = False
    Application.ScreenUpdating = False

    For Each rRow In rRowRange.Rows
        bHide = False

        For Each rCell In rRow.Cells

            If Len(rCell.Value) = 0 Then 'check here if cell has a value other than ""
                bHide = True
                Exit For
            End If
        Next rCell
        rRow.EntireRow.Hidden = bHide

    Next rRow

    Application.ScreenUpdating = True

End Sub

Open in new window

Author

Commented:
great, thank you

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial