VBA to Hide/Unhide Cells with Zero

  • Hi there,


    What I am attempting to do is create one VBA script attached to a button that will toggle to either hide or unhide cells based on it containing zero. I have created the simply script below that works but it takes to long to run as it goes line by line which you can see happening right before your eyes.


    Code
    Sub ToggleHideRows()
        Dim c As Range
        For Each c In Range("P76:P500")
            If Not IsEmpty(c) And c.Value = 0 Then
                c.EntireRow.Hidden = Not c.EntireRow.Hidden
            End If
        Next
    End Sub



    To try and speed things up I pieced together the following script but I could only get it to hide ALL the zeros in column "P" instead of a selected portion such as P76:P500. Then it would need to have code to toggle back and forth between hiding and unhiding zeros which I couldn't figure out.





    Thank You and look forward to your response.

  • Try turning off screen updating.

    Code
    Sub ToggleHideRows()
        Dim c As Range
        Application.ScreenUpdating = False
        For Each c In Range("P76:P500")
            If Not IsEmpty(c) and c.Value = 0 Then
                c.EntireRow.Hidden = Not c.EntireRow.Hidden
            End If
        Next
        Application.ScreenUpdating = True
    End Sub
  • Hi Stephen,


    Thanks, that did speed things up.


    Anyway for it to be even faster? Only asking as the second code I submitted was less then a second to work but it wasn't working as I intended it.




    Thanks again.

  • You could try turning off calculations too (as per your second bit of code).


    If your data are in a continuous block you could use AutoFilter. That would avoid a loop altogether.


    Limit your range to that containing data (in case it doesn't go down to row 500).


    Do you have other code because that code was pretty much instantaneous for me?

  • For the faster can use UNION

    Code
    Sub ToggleHideRows()
        Dim c As Range, U As Range
        For Each c In Range("P76:P90")
            If c.Value = 0 Then
                Set U = Union(IIf(U Is Nothing, c, U), c)
            End If
        Next
        If Not U Is Nothing Then U.EntireRow.Hidden = True
    End Sub
  • Stephen,


    When I use the following code it works instantaneous but two things. I need to run the code between P76:P500 (not just the P column), then I need to toggle back to unhide the zeros by running the same code. Is that possible?




    Thanks,

Participate now!

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