Macro to hide rows on multiple sheets

[Rusty315]

Hi everyone

I'm new to VBA (which is to say I have no idea what I'm doing). I'm trying to create a macro that will hide empty rows on various worksheets when the workbook is opened. I have a similar piece of code that I've used successfully in another workbook:

Private Sub Workbook_Open()

Dim myRng As Range
Dim myCell As Range
With Worksheets("League Table")
Set myRng = .Range("B3:B83")
For Each myCell In myRng.Cells
myCell.EntireRow.Hidden = CBool(myCell.Value = "")
Next myCell
End With

Dim myCell1 As Range
With Worksheets("Team Summaries")
Set myRng = .Range("B14:B34")
For Each myCell In myRng.Cells
myCell.EntireRow.Hidden = CBool(myCell.Value = "0")
Next myCell
End With

End Sub

I need to use this code in a different workbook where I have around 30 pages for individual sales people in our team with all sheets formatted in exactly the same way. I would like to make this code work on all of those sheets but I don't want to have to tinker with this macro every time someone new joins the team so I'm wondering if I can make this macro apply to every sheet in the workbook without having to name each sheet individually in the macro which would mean having to change the macro every time someone joins or leaves.


Any help anyone can give me would be greatly appreciated.

Cheers!

 

[Andrew Poulsom]

Try eg:

Private Sub Workbook_Open()
    Dim ws As Worksheet
    Dim myRng As Range
    Dim myCell As Range
    For Each ws In ThisWorkbook.Worksheets
        With ws
            Set myRng = .Range("B3:B83")
            For Each myCell In myRng.Cells
                myCell.EntireRow.Hidden = CBool(myCell.Value = "")
            Next myCell
        End With
    Next ws
End Sub