Results 1 to 10 of 10

Thread: Hide Rows Based on Cell Value

  1. #1
    New Member
    Join Date
    Mar 2015
    Posts
    17
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Hide Rows Based on Cell Value

    Hey everyone,

    I am using the following code to hide rows in a workbook where column A has the value "Hide". However going through 500 lines seems to take a long time. I am wondering if there is a better way to have excel search and select all rows where the value of column A is "Hide" and hide the rows all at once instead of going through each line one at a time.

    Any suggestions?

    Code:
    Sub Hide_Rows()
    'Unhide all Rows Before Beginning
    Call Unhide
    
    
    'Define Row Numbers to Examine
    For rownum = 1 To 500
    
    
    'If Statement
        If ActiveSheet.Cells(rownum, 1).Value = "Hide" Then
    
    
            ActiveSheet.Rows(rownum).Hidden = True
    
    
        End If
    
    
    'Loop
    Next
    
    
    End Sub
    Thanks in advance for your help.

    -Stephen

  2. #2
    Board Regular Michael M's Avatar
    Join Date
    Oct 2005
    Location
    South Western NSW
    Posts
    17,812
    Post Thanks / Like
    Mentioned
    18 Post(s)
    Tagged
    2 Thread(s)

    Default Re: Hide Rows Based on Cell Value

    Try

    Code:
    Sub MM1()
      With Range("A1", Cells(Rows.Count, "A").End(xlUp))
        .Replace "Hide", "#N/A", xlWhole, , False, , False, False
        Columns("A").SpecialCells(xlConstants, xlErrors).EntireRow.Hidden = True
      End With
    End Sub
    Regards
    Michael M
    ---------------------------------------
    The more I learn, the less I seem to know.....A Please and Thank You cost nothing !
    It's easier to debug if we can see the whole macro !
    Home 2007 & 2013

    - Posting guidelines, forum rules and terms of use

    - To download Mr Excel HTML Maker

    - Try searching for your answer first, see how

    - Read the FAQs

    - List of BB codes


    [CODE]Place Your Code Here[/CODE]

  3. #3
    Board Regular Logit's Avatar
    Join Date
    Aug 2016
    Location
    United States
    Posts
    2,806
    Post Thanks / Like
    Mentioned
    36 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Hide Rows Based on Cell Value

    .
    How long is your macro taking ?

    This macro hid all rows in under 1 sec. I populated 1,000 rows.

    Code:
    Sub HideRows()
    Dim ws As Worksheet
    Set ws = ThisWorkbook.Sheets("sheet1")
    
    
    wsLR = ws.Cells(Rows.Count, 1).End(xlUp).Row
    
    
    For x = 2 To wsLR
       
        If ws.Cells(x, 1).Value = "Hide" Then  
            'hide
            ws.Range("a" & x).EntireRow.Hidden = True
        End If
    Next x
    
    
    End Sub

  4. #4
    New Member
    Join Date
    Mar 2015
    Posts
    17
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Hide Rows Based on Cell Value

    Thanks for your quick response.

    Is there a way to do this without changing the values? They are formulated and which rows need to be hidden will change.

  5. #5
    New Member
    Join Date
    Mar 2015
    Posts
    17
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Hide Rows Based on Cell Value

    Quote Originally Posted by Logit View Post
    .
    How long is your macro taking ?

    This macro hid all rows in under 1 sec. I populated 1,000 rows.

    Code:
    Sub HideRows()
    Dim ws As Worksheet
    Set ws = ThisWorkbook.Sheets("sheet1")
    
    
    wsLR = ws.Cells(Rows.Count, 1).End(xlUp).Row
    
    
    For x = 2 To wsLR
       
        If ws.Cells(x, 1).Value = "Hide" Then  
            'hide
            ws.Range("a" & x).EntireRow.Hidden = True
        End If
    Next x
    
    
    End Sub
    My code worked well when I dropped it into a very basic workbook to test it. But when I ran it in a workbook with much more data in it, even at 500 lines, it takes almost a full minute to run. I have four sheets in the workbook to run it in, and that starts to add up.

    I have been tinkering and there is a way to use the find function and select all of the cells, I am just not sure how then select the full rows and hide them using VBA. But I am open to other ideas.

    Thanks again for your help. Much appreciate the brainstorming.

  6. #6
    Board Regular Logit's Avatar
    Join Date
    Aug 2016
    Location
    United States
    Posts
    2,806
    Post Thanks / Like
    Mentioned
    36 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Hide Rows Based on Cell Value

    .
    I suspect your workbook sheets may have "invisible data" residing in a large segment of rows on your worksheets.
    Sometimes, in my experience, when the workbook encounters several errors or even for no reason at all (ain't Excel wonderful ?)
    garbage data that is invisible can accumulate in numerous rows below those rows you CAN see data in the cells.

    Make a COPY OF YOUR EXISTING workbook and try this macro on it. If it clears up the issue on your COPY then you can try it on
    the real workbook :

    Code:
    Sub LipoSuction()
    'JBeaucaire (8/3/2009)
    Dim LR As Long, LC As Long
    Dim ws As Worksheet
     
    For Each ws In Worksheets
        LR = ws.Range("A" & ws.Rows.Count).End(xlUp).Row + 1
        LC = ws.Cells(1, ws.Columns.Count).End(xlToLeft).Column + 1
     
        'Clear everything below column A last cell and row 1 last cell
            ws.Range(ws.Cells(1, LC), ws.Cells(ws.Rows.Count, ws.Columns.Count)).Clear
            ws.Range(ws.Cells(LR, 1), ws.Cells(ws.Rows.Count, ws.Columns.Count)).Clear
    Next ws
     
    End Sub

    Basically what the macro does is select all the rows below your VISIBLE data and clears the contents. You can do it manually if you
    like but why ???

  7. #7
    Board Regular Michael M's Avatar
    Join Date
    Oct 2005
    Location
    South Western NSW
    Posts
    17,812
    Post Thanks / Like
    Mentioned
    18 Post(s)
    Tagged
    2 Thread(s)

    Default Re: Hide Rows Based on Cell Value

    Try starting from the last row up.....rather than top down
    Code:
    Sub HideRows()
    Dim ws As Worksheet, x as long
    appilcation.screenupdating=false
    Set ws = ThisWorkbook.Sheets("sheet1")
    wsLR = ws.Cells(Rows.Count, 1).End(xlUp).Row
    For x = wsLR to 2 step -1
        If ws.Cells(x, 1).Value = "Hide" Then  
            'hide
            ws.Range("a" & x).EntireRow.Hidden = True
        End If
    Next x
    appilcation.screenupdating=true
    End Sub
    Last edited by Michael M; Sep 6th, 2019 at 10:52 PM.
    Regards
    Michael M
    ---------------------------------------
    The more I learn, the less I seem to know.....A Please and Thank You cost nothing !
    It's easier to debug if we can see the whole macro !
    Home 2007 & 2013

    - Posting guidelines, forum rules and terms of use

    - To download Mr Excel HTML Maker

    - Try searching for your answer first, see how

    - Read the FAQs

    - List of BB codes


    [CODE]Place Your Code Here[/CODE]

  8. #8
    Board Regular
    Join Date
    Sep 2004
    Posts
    1,379
    Post Thanks / Like
    Mentioned
    9 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Hide Rows Based on Cell Value

    You might try this also. I assume you have a header in the first row.
    Code:
    Sub AAAAB_Select_Hide()
    With Range("A1:A" & Cells(Rows.Count, 1).End(xlUp).Row)
      .AutoFilter 1, "Hide"
      .Offset(1).SpecialCells(12).Select
      .AutoFilter 1
    End With
    Selection.Rows.Hidden = True
    End Sub
    Theory is when you know something, but it doesnít work. Practice is when something works, but you donít know why. Politicians combine theory and practice: nothing works and they donít know why.

  9. #9
    New Member
    Join Date
    Mar 2015
    Posts
    17
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Hide Rows Based on Cell Value

    Thanks for all the help. I tried a number of different suggestions and the only one that did not take a while to run was to put an auto-filter on column A and filter out (hide) the rows with "Hide" as the value.

    Thanks again to everyone that commented.

  10. #10
    Board Regular Logit's Avatar
    Join Date
    Aug 2016
    Location
    United States
    Posts
    2,806
    Post Thanks / Like
    Mentioned
    36 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Hide Rows Based on Cell Value

    .
    You are welcome.

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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