Hide Row based on cell value only on specific sheets

[concenhr23]

Hi,

I have tried to loop each sheet based on a sheet list that is in one tab. This list can change from month-to-month so I would like to avoid going in VBE and make the changes. Below is the code, it hides the rows in Tab A but not the others.

Tab1 (Master) - Sheet List starts at A2, (Name Range = SheetNameList)

Tab 2-7 Tab A through G.

For each sheet (SheetNameList) in Col. A (starting from A2)

If Value is 1, then hide the entire row

If Value is 0, if not previously hidden then unhide.

Sub hiderows()
Dim MS As Worksheet
Dim rng As Range, a As Range
Dim rCell As Range

Set rng = ActiveWorkbook.Sheets("MS").Range("SheetNameList")

For Each MS In ActiveWorkbook.Worksheets
If Not IsError(Application.Match(MS.Name, rng, 0)) Then
LastRow = Cells(Cells.Rows.Count, "A").End(xlUp).Row
On Error Resume Next
For Each a In Range("A2:A" & LastRow)
   If a.Value > 0 Then
        a.EntireRow.Hidden = True
    ElseIf a.Value = 0 Then
        a.EntireRow.Hidden = False
    End If
Next
End If
Next MS
End Sub

 

[dmt32]

Hi,
The Ranges in your For Next Loops are not qualified to your variable MS -so code is applied to the active sheet only

Untested but see if this update helps

Sub hiderows()
    Dim ws As Worksheet
    Dim rng As Range, cell As Range
    Dim lastrow As Long
    
    Set rng = ThisWorkbook.Sheets("MS").Range("SheetNameList")
    
    For Each ws In ThisWorkbook.Worksheets
        If Not IsError(Application.Match(ws.Name, rng, 0)) Then
            lastrow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
            
            For Each cell In ws.Range("A2:A" & lastrow)
                cell.EntireRow.Hidden = CBool(cell.Value > 0)
            Next cell
        End If
    Next ws
End Sub

Dave

 

[concenhr23]

Hi,
The Ranges in your For Next Loops are not qualified to your variable MS -so code is applied to the active sheet only

Untested but see if this update helps

Sub hiderows()
    Dim ws As Worksheet
    Dim rng As Range, cell As Range
    Dim lastrow As Long
   
    Set rng = ThisWorkbook.Sheets("MS").Range("SheetNameList")
   
    For Each ws In ThisWorkbook.Worksheets
        If Not IsError(Application.Match(ws.Name, rng, 0)) Then
            lastrow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
           
            For Each cell In ws.Range("A2:A" & lastrow)
                cell.EntireRow.Hidden = CBool(cell.Value > 0)
            Next cell
        End If
    Next ws
End Sub

Dave

This works out perfectly!

Thank you!!

 

[dmt32]

This works out perfectly!

Thank you!!

most welcome - thanks for feedback

Dave