Loading
Ozgrid Excel Help & Best Practices Forums

Excel Training / 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:

    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!

    Excel Video Tutorials / Excel Dashboards Reports


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

    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,788

    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,788

  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.

    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

    Excel Video Tutorials / Excel Dashboards Reports


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

    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,071

    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.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, 11:30
  2. Hide/Unhide Rows Meeting Condition In Column
    By caliskier in forum EXCEL HELP
    Replies: 5
    Last Post: August 22nd, 2008, 10:52
  3. Macro Code Automatically To Hide Rows Based Condition
    By jazztalker in forum EXCEL HELP
    Replies: 3
    Last Post: April 26th, 2008, 09:14
  4. Hide Rows Based On Condition - Multiple Sheets
    By jwilso6 in forum EXCEL HELP
    Replies: 7
    Last Post: March 7th, 2008, 07:21
  5. Hide & Unhide Rows Based On Condition
    By jimbean in forum EXCEL HELP
    Replies: 4
    Last Post: September 5th, 2007, 02: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