Loading
Ozgrid Excel Help & Best Practices Forums

Excel Video Tutorials / Excel Dashboards Reports



Page 1 of 2 1 2 LastLast
Results 1 to 10 of 15

Thread: Hide multiple non-sequential rows simultaneously based on condition of cell

  1. #1
    Join Date
    25th January 2013
    Location
    London
    Posts
    7

    Hide multiple non-sequential rows simultaneously based on condition of cell

    I am looking for a way to simultaneously hide multiple, non-sequential rows based on a value in the rows I want to hide.

    In each worksheet there is a single column named range and each cell in the range contains the value 1 or 2, depending on some other calculation. I then want to hide those rows whose value in the range contain the value 2.

    The key word of my question is "simultaneously". I have code that will do exactly what I describe, but it does it one row at a time. There are over 20,000 rows and this can take quite some time.

    I figure that if it just hid all of the cells that have a "2" (or that don't contain the number "1") at the same time it would be substantially faster (I hope I'm right).

    Here's the code I am currently using. The code is set up to be called by various different macros in the model, each pointing to a different named range, where strRange represents the name of the range that will be checked for 1 & 2's. The named ranges (strRange) are in different worksheets. Rather than repeat the code over and over I simply created this one set of code to be used repeatedly.

    VB:
    Sub ExpandHideRows(strRange As String) 
         
        Dim ExHR As Range 
         
        Set ExHR = Range(strRange) 
        ExHR.EntireRow.Hidden = False 'code first expands all rows prior to hiding rows containing value "2"
         
        For Each ExHR In Range(strRange) 
            If ExHR.Value = 2 Then 
                ExHR.EntireRow.Hidden = True 
            End If 
        Next ExHR 
         
        Set ExHR = Nothing 
         
    End Sub 
    
    
    Thanks for the help.

    Excel Video Tutorials / Excel Dashboards Reports


  2. #2
    Join Date
    23rd October 2003
    Location
    France Alsace
    Posts
    3,616

    Re: Hide multiple non-sequential rows simultaneously based on condition of cell

    If you were accepting to change the value 2 to a character (e.g; "A") next code could be faster.
    Please comment
    VB:
    Sub ExpandHideRows1(strRange As String) 
        Dim ExHR As Range 
         
        Application.ScreenUpdating = False 
        Set ExHR = Range(strRange) 
        ExHR.EntireRow.Hidden = False 'code first expands all rows prior to hiding rows containing value "2"
         
        Range(strRange).SpecialCells(xlCellTypeConstants, 2).EntireRow.Hidden = True 
        Set ExHR = Nothing 
        Application.ScreenUpdating = True 
    End Sub 
    
    
    Triumph without peril brings no glory: Just try

  3. #3
    Join Date
    26th January 2003
    Location
    Derbyshire,UK
    Posts
    17,906

    Re: Hide multiple non-sequential rows simultaneously based on condition of cell

    The quickest way would be with AutoFilter. Does your data have a header row?
    Hope that Helps

    Roy

    For free Excel tools & articles visit my web site

    If I have helped you and you feel like putting your hand in your pocket please make a donation to Children in Need

    RoyUK's Web Site

    royUK's Database Form

    Where to paste code from the Forum

    About me.

  4. #4
    Join Date
    19th July 2004
    Location
    Tokyo, Japan
    Posts
    8,275

    Re: Hide multiple non-sequential rows simultaneously based on condition of cell

    Quote Originally Posted by royUK View Post
    The quickest way would be with AutoFilter. Does your data have a header row?
    I was just about to.....

    VB:
    Sub ExpandHideRows1(strRange As String) 
        With Range(strRange) 
            .Parent.AutoFilterMode = False 
            .AutoFilter 1, "<>2", , , 0 
        End With 
    End Sub 
    
    

    Excel Video Tutorials / Excel Dashboards Reports


  5. #5
    Join Date
    25th January 2013
    Location
    London
    Posts
    7

    Re: Hide multiple non-sequential rows simultaneously based on condition of cell

    Thanks for the replies so far.

    I can't use autofilter as it is not a pure data table I am playing with (it's more of an input form with some formulas, drop down boxes, etc.) and the order cannot be changed.

    PCI, as for the use of "A" instead of the value "2", I will need to check if that's possible. It may imply too many changes to the model, but I'm not sure yet. I will look into it and maybe that's a solution.

    Any other suggestions out there? Thanks again.

    Excel Video Tutorials / Excel Dashboards Reports


  6. #6
    Join Date
    25th January 2013
    Location
    London
    Posts
    7

    Re: Hide multiple non-sequential rows simultaneously based on condition of cell

    Solution has been found.

    With help from another Excel forum, the following was provided and works great.

    It has taken the run time down to 6 seconds. It was previously taking upwards of 5 minutes!

    PCI, ultimately changing the 2 to an A was not viable, it impacted too many other parts of the model and the risk was to screw it up without knowing it. But your help is much appreciated.

    Here's the code. I hope this helps someone else in the future.

    VB:
    Sub ExpandHideRows(strRange As String) 
        Dim ExHR As Range 
        Dim rHide As Range: Set rHide = Nothing 
        Set ExHR = Range(strRange) 
        ExHR.EntireRow.Hidden = False 'code first expands all rows prior to hiding rows containing value "2"
        For Each ExHR In Range(strRange) 
            If ExHR.Value = 2 Then 
                If rHide Is Nothing Then 
                    Set rHide = ExHR 
                Else 
                    Set rHide = Union(ExHR, rHide) 
                End If 
            End If 
        Next ExHR 
         
         
        If rHide Is Nothing Then 
        Else 
            rHide.EntireRow.Hidden = True 
            Set ExHR = Nothing 
        End If 
    End Sub 
    
    

    Excel Video Tutorials / Excel Dashboards Reports


  7. #7
    Join Date
    26th January 2003
    Location
    Derbyshire,UK
    Posts
    17,906

    Re: Hide multiple non-sequential rows simultaneously based on condition of cell

    It's good you have a solution, but you obviously cross posted this which is frowned on by all Excel Forums.
    Hope that Helps

    Roy

    For free Excel tools & articles visit my web site

    If I have helped you and you feel like putting your hand in your pocket please make a donation to Children in Need

    RoyUK's Web Site

    royUK's Database Form

    Where to paste code from the Forum

    About me.

  8. #8
    Join Date
    19th July 2004
    Location
    Tokyo, Japan
    Posts
    8,275

    Re: Hide multiple non-sequential rows simultaneously based on condition of cell

    And
    Quote Originally Posted by HDF
    I can't use autofilter as it is not a pure data table I am playing with (it's more of an input form with some formulas, drop down boxes, etc.) and the order cannot be changed.


    I don't understand such reason.

    Excel Video Tutorials / Excel Dashboards Reports


  9. #9
    Join Date
    25th January 2013
    Location
    London
    Posts
    7

    Re: Hide multiple non-sequential rows simultaneously based on condition of cell

    My understanding of autofilter (and I admit that I don't properly understand all that autofilter can do) is that it is for use in tables that have headings across the top and all of the data is contiguous - or with some gaps - in the rows below the headings.

    My worksheet is not a data table as such. It is a ranges in separate groupings, each grouping containing input cells, formulas, drop down boxes, etc. I don't see how you could autofilter that. But I admit that I am not completely familiar with all of its functionality.

    In any case, the solution I found does exactly what I needed.

    Thanks for the follow up.

    Excel Video Tutorials / Excel Dashboards Reports


  10. #10
    Join Date
    19th July 2004
    Location
    Tokyo, Japan
    Posts
    8,275

    Re: Hide multiple non-sequential rows simultaneously based on condition of cell

    We don't care if you prefer inefficient solution, but you shouldn't make any meaningless excuse.

    Viewers may get confused.

    Excel Video Tutorials / Excel Dashboards Reports


Thread Information

Users Browsing this Thread

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

Possible Answers

  1. Excel macro to hide rows based on condition
    By bort11 in forum EXCEL HELP
    Replies: 5
    Last Post: December 1st, 2011, 00:08
  2. Hide/Show Multiple Rows Based On Cell Value
    By freshna in forum EXCEL HELP
    Replies: 12
    Last Post: November 20th, 2010, 04:21
  3. Fast Way To Hide Rows Based On Condition
    By juanb77 in forum EXCEL HELP
    Replies: 7
    Last Post: May 9th, 2008, 16:24
  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