Announcement

Collapse
No announcement yet.

Fast Way To Hide Rows Based On Condition

Collapse
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • Fast Way To Hide Rows Based On Condition



    Hi everyone. I been trying to speed up the process of hiding rows based on a condition, but with no luck..

    In detail I have a model with about 12 sheets, after changing some of the inputs I need to go to the Summary sheet and get my report. This report varies depending on the inputs, and some rows in the summary need to be hidden. In the Summary sheet, I use column B to set up a condition to hide or show the row. If the cell in colum b has 0 then I need the macro to hide that row.

    I been using this code:

    Code:
    Sub Hide_Rows()
    ' go to summary and unhide all
    
        Sheets("summary").Select
        Cells.Select
        Selection.EntireRow.Hidden = False
    
    ' hide rows    
        Dim i As Integer
        For i = 3 To 500
        If Range("B" & i).Value = 0 Then
        Rows(i & ":" & i).EntireRow.Hidden = True
        End If
        Next i
        
    End Sub
    The problem with the code above is that its not very efficient as it has to go row by row. I found in another post a variation that works great but it goes to all the worksheets in the file. I have tried erasing all the parts that reference the worksheet but I get an error. How can I change this to stay in a single worksheet?

    Code:
    Sub Hide_Rows2()
        
        Sheets("summary").Select
        Cells.Select
        Selection.EntireRow.Hidden = False
        
        
        Dim Wsht As Worksheet
        Dim i As Integer
        
        Application.ScreenUpdating = False
         
        For Each Wsht In Worksheets
            With Wsht
                For i = 3 To 500
                    Select Case .Range("b" & i).Value
                    Case "0"
                        .Rows(i & ":" & i).EntireRow.Hidden = True
                    End Select
                Next i
            End With
       Next Wsht
         
        Application.ScreenUpdating = True
    End Sub
    Thanks in advance!

  • #2
    Re: Hide Multiple Rows Based On Condition

    You shouldn't use a loop, but use an autofilter. This is about the fastest you can do (for a moderate to large number of rows to process).

    Wigi
    Regards,

    Wigi

    Excel MVP 2011-2014

    For more Excel memes: visit http://www.wimgielis.com ==> English articles ==> Excel memes

    -- Topics without [CODE] tags or a meaningful topic title, will be neglected by me (and probably many others as well) --

    Comment


    • #3
      Re: Hide Multiple Rows Based On Condition

      Assumed Experience: Well above average
      Lower it please.

      Comment


      • #4
        Re: Fast Way To Hide Rows Based On Condition

        Hey Dave you are correct and just changed it.

        Wigi, yes autofilter works, but I still wonder what needs to be changed in the second macro in order to work in just one worksheet instead of going to all. The second macro works amazingly fast and it would solve what I want to do.

        Thanks

        Comment


        • #5
          Re: Fast Way To Hide Rows Based On Condition

          Take out the loops and use the sheet needed.

          Comment


          • #6
            Re: Fast Way To Hide Rows Based On Condition

            I just tried the autofilter, I didn't know I could controlled with a macro. It works well.

            Code:
            Sub Hide_Rows3()
            
                Sheets("summary").Select
                
                Range("B2:J500").Select
                Selection.AutoFilter
                Selection.AutoFilter Field:=1, Criteria1:="1"
            End Sub
            These are the modifications I make to the macro with a problem, it still doesn't work. Sorry if I keep on this issue, I just want to learn.

            Code:
            Sub Hide_Rows2()
                 
                Sheets("summary").Select
                Cells.Select
                Selection.EntireRow.Hidden = False
                 
                 
                '*ERASE* Dim Wsht As Worksheet
                
                Dim i As Integer
                Application.ScreenUpdating = False
                 
                '*ERASE* For Each Wsht In Worksheets
                '*ERASE* With Wsht
                        For i = 3 To 500
                            Select Case .Range("b" & i).Value
                            Case "0"
                                .Rows(i & ":" & i).EntireRow.Hidden = True
                            End Select
                        Next i
                '*ERASE* End With
                '*ERASE* Next Wsht
                 
                Application.ScreenUpdating = True
            End Sub

            Comment


            • #7
              Re: Fast Way To Hide Rows Based On Condition

              Hover over AUTOFILTER and follow the links and other links on the page.

              Comment


              • #8


                Re: Fast Way To Hide Rows Based On Condition

                w.r.t. the loop, use e.g.

                Code:
                Sub Hide_Rows2()
                     
                    Dim i As Integer
                    
                    Application.ScreenUpdating = False
                    
                    With Sheets("summary")
                        
                        .Cells.EntireRow.Hidden = False
                        
                        For i = 3 To 500
                            Select Case .Range("b" & i).Value
                            Case 0
                                .Rows(i & ":" & i).EntireRow.Hidden = True
                            End Select
                        Next i
                         
                    End With
                         
                    Application.ScreenUpdating = True
                    
                End Sub
                Wigi
                Regards,

                Wigi

                Excel MVP 2011-2014

                For more Excel memes: visit http://www.wimgielis.com ==> English articles ==> Excel memes

                -- Topics without [CODE] tags or a meaningful topic title, will be neglected by me (and probably many others as well) --

                Comment

                Working...
                X