Announcement

Collapse
No announcement yet.

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

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

  • 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.

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

  • #2
    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
    Code:
    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

    Comment


    • #3
      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

      New users should read the Forum Rules before posting

      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.

      Comment


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

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

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

        Comment


        • #5
          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.

          Comment


          • #6
            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.

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

            Comment


            • #7
              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

              New users should read the Forum Rules before posting

              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.

              Comment


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

                And
                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.

                Comment


                • #9
                  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.

                  Comment


                  • #10
                    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.

                    Comment


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

                      Originally posted by jindon View Post
                      We don't care if you prefer inefficient solution, but you shouldn't make any meaningless excuse.
                      You seem to have been offended (or since you are using "we" apparently I have offended more than just you) by something I've written, as otherwise I can't understand all of the hostility you are expressing.

                      I have found a solution that is extremely effective for my needs and have so stated - providing that solution for others to try, if they so desire/require. I have also stated that I don't have enough knowledge of the autofilter function to determine if it would indeed work as well. When I tried it, it did not work, but I saw no point in bringing that up as I did not spend sufficient time playing with it to try and make it work, as I found an alternative viable solution.


                      Have a good day.

                      Comment


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

                        As you say, if the data is not arranged in a table format then AutoFilter will not help.
                        Hope that Helps

                        Roy

                        New users should read the Forum Rules before posting

                        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.

                        Comment


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

                          Originally posted by royUK View Post
                          As you say, if the data is not arranged in a table format then AutoFilter will not help.
                          Roy, thanks, you confirmed what I thought about autofilter.

                          Any idea on why the hostility from jindon?

                          And as for the cross forum posting, I remember reading in the rules something about cross posting, but understood that to mean cross posting in other threads in the same domain (e.g. ozgrid.com). I did not understand it as not asking the same question elsewhere in order to get advice from others who may not participate in this particular forum.

                          Actually, I just re-read the rules. It says I should provide a link to the other post in mine if I do cross post. I was going to do that now, but I seem no longer able to edit that post.

                          Comment


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

                            http://answers.microsoft.com/en-us/o...d-06c2421b4c5a

                            Read this... Message to cross posters

                            Comment


                            • #15


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

                              Thanks, duly noted.

                              Comment

                              Working...
                              X