Loading
Ozgrid Excel Help & Best Practices Forums

Excel Video Tutorials / Excel Dashboards Reports



Results 1 to 8 of 8

Thread: Fast Way To Hide Rows Based On Condition

  1. #1
    Join Date
    27th July 2004
    Location
    Vancouver
    Posts
    4

    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:

    VB:
    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?

    VB:
    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!

    Excel Video Tutorials / Excel Dashboards Reports


  2. #2
    Join Date
    15th August 2005
    Location
    Mechelen, Belgium
    Posts
    6,062

    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.be ==> English articles ==> Excel memes

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

  3. #3
    Join Date
    24th January 2003
    Location
    Australia
    Posts
    31,717

    Re: Hide Multiple Rows Based On Condition

    Assumed Experience: Well above average
    Lower it please.

  4. #4
    Join Date
    27th July 2004
    Location
    Vancouver
    Posts
    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

    Excel Video Tutorials / Excel Dashboards Reports


  5. #5
    Join Date
    24th January 2003
    Location
    Australia
    Posts
    31,717

  6. #6
    Join Date
    27th July 2004
    Location
    Vancouver
    Posts
    4

    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.

    VB:
    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.

    VB:
    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 
    
    

    Excel Video Tutorials / Excel Dashboards Reports


  7. #7
    Join Date
    24th January 2003
    Location
    Australia
    Posts
    31,717

    Re: Fast Way To Hide Rows Based On Condition

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

  8. #8
    Join Date
    15th August 2005
    Location
    Mechelen, Belgium
    Posts
    6,062

    Re: Fast Way To Hide Rows Based On Condition

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

    VB:
    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.be ==> English articles ==> Excel memes

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

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Possible Answers

  1. Replies: 4
    Last Post: March 14th, 2009, 10:30
  2. Hide/Unhide Rows Meeting Condition In Column
    By caliskier in forum EXCEL HELP
    Replies: 5
    Last Post: August 22nd, 2008, 09:52
  3. Macro Code Automatically To Hide Rows Based Condition
    By jazztalker in forum EXCEL HELP
    Replies: 3
    Last Post: April 26th, 2008, 08:14
  4. Hide Rows Based On Condition - Multiple Sheets
    By jwilso6 in forum EXCEL HELP
    Replies: 7
    Last Post: March 7th, 2008, 06:21
  5. Hide & Unhide Rows Based On Condition
    By jimbean in forum EXCEL HELP
    Replies: 4
    Last Post: September 5th, 2007, 01:39

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
porno