Hide rows with specific values on all sheets

[christianbiker]

Greetings,

I found this code I believe through MrExcel and am wondering how I can conduct this search on all worksheets, not just one?

    BeginRow = 1
    EndRow = 300
    ChkCol = 1

    For RowCnt = BeginRow To EndRow
        If Cells(RowCnt, ChkCol).Value = "HIDE" Then
            Cells(RowCnt, ChkCol).EntireRow.Hidden = True
        End If
    Next RowCnt

Thanks!

 

[Michael M]

This should do what you need....BUT...you haven't supplied ALL of the code, so there may be other issues !!!

Sub MM1()
Dim ws As Worksheet
Application.ScreenUpdating = False
 BeginRow = 1
    EndRow = 300
    ChkCol = 1
For Each ws In Worksheets
    ws.Activate
    For RowCnt = BeginRow To EndRow
        If Cells(RowCnt, ChkCol).Value = "HIDE" Then
            Cells(RowCnt, ChkCol).EntireRow.Hidden = True
        End If
    Next RowCnt
Next ws
Application.ScreenUpdating = True
End Sub

 

[My Aswer Is This]

I suspect you want to find all values in Column A of all sheets.
Not just rows 1 to 300 as each sheet may not have the same number rows.
So try this:

Sub Hide_Rows()
'Modified 3/24/2020 12:09:22 AM EST
Application.ScreenUpdating = False
Dim i As Long
Dim Lastrow As Long
Dim b As Long
For i = 1 To Sheets.Count
With Sheets(i)
Lastrow = .Cells(Rows.Count, "A").End(xlUp).Row
For b = 1 To Lastrow
If .Cells(b, 1).Value = "Hide" Then .Rows(b).Hidden = True
Next
End With
Next
Application.ScreenUpdating = True
End Sub

 

[Michael M]

@MAIT
The OP has advised that he is using Col "A" in his code snippet

BeginRow = 1
EndRow = 300
ChkCol = 1

 

[My Aswer Is This]

@MAIT
The OP has advised that he is using Col "A" in his code snippet

BeginRow = 1
EndRow = 300
ChkCol = 1

That what my script is doing:
If .Cells(b, 1).Value = "Hide" Then .Rows(b).Hidden = True

 

[Michael M]

But you stated

I suspect you want to find all values in Column A of all sheets.

which he has already alluded to !

 

[Peter_SSs]

But you stated

I suspect you want to find all values in Column A of all sheets.

which he has already alluded to !

Michael, I think you need to read that line with an emphasis on the word "all" and in conjunction with what followed. MAIT's point was about whether every sheet would have 300 rows or not, rather than what columns were to be used.