Hide Rows Based on Cell Value

[bonescoster]

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?

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

 

[Michael M]

Try

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

 

[Logit]

.
How long is your macro taking ?

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

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

 

[bonescoster]

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.

 

[bonescoster]

.
How long is your macro taking ?

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

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.

 

[Logit]

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

[B]Sub LipoSuction()[/B]
'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 ???

 

[Michael M]

Try starting from the last row up.....rather than top down

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

 

[jolivanes]

You might try this also. I assume you have a header in the first row.

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

 

[bonescoster]

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.

 

[Logit]

.
You are welcome.