Hide Rows Based on Cell Value

bonescoster

New Member
Joined
Mar 16, 2015
Messages
18
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
 

Excel Facts

Quick Sum
Select a range of cells. The total appears in bottom right of Excel screen. Right-click total to add Max, Min, Count, Average.
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
 
Upvote 0
.
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
 
Upvote 0
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.
 
Upvote 0
.
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.
 
Upvote 0
.
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:
[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 ???
 
Upvote 0
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:
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,224,819
Messages
6,181,153
Members
453,021
Latest member
Justyna P

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top