Excel VBA to hide rows based on cell value in range

[Stuepef]

I am looking for assistance in revising code for one of my worksheets (Sheet3). In essence, I want to look at a specific cell within a range and if that cell is blank, then hide the entire range. Sheet3 has 5 ranges to gauge whether to hide the blank rows and thought a Union would be the best option. The Sheet's ranges are as follows:

Range 1

1. A11:A60 - Hide rows that are blank in range (A11 will never be blank, but the rest of the range could be blank and would need hiding)

Range 2

1. A71:A120 - If cell A71 is blank, Hide A65:A122. Otherwise hide all rows that are blank in range A71:A120.

Range 3

1. A131:A180 - If cell A131 is blank, Hide A125:A182. Otherwise hide all rows that are blank in range A131:A180.

Range 4

1. A191:A240 - If cell A191 is blank, Hide A185:A242. Otherwise hide all rows that are blank in range A191:A240.

Range 5

1. A251:A300 - If cell A251 is blank, Hide A246:A302. Otherwise hide all rows that are blank in range A251:A300.

Dim wsMySheet As Worksheet
    Dim lngMyRow  As Long, unionRng As Range
    Application.ScreenUpdating = False
    For Each wsMySheet In ThisWorkbook.Sheets
       Select Case wsMySheet.Name
        Case Is = Sheet3
            .Range("A11:A60", "A71:120", "A131:A180", "A191:A240", "A251:A300").EntireRow.Hidden = False
                For lngMyRow = 11 To 60
                    If Len(.Range("A" & lngMyRow)) = 0 Then
                        If Not unionRng Is Nothing Then
                            Set unionRng = Union(unionRng, .Range("A" & lngMyRow))
                        Else
                            Set unionRng = .Range("A" & lngMyRow)
                        End If
                    End If
                Next lngMyRow
            End With
        End Select
        If Not unionRng Is Nothing Then unionRng.EntireRow.Hidden = True
        Set unionRng = Nothing
    Next wsMySheet
    Application.ScreenUpdating = True
[FONT=inherit]End Sub[/FONT]

 

[Fluff]

What is the name of the sheet you want this to work on?
Also, are the cells in Col A that you want to look at actually blank or do they contain a formula that returns ""

 

[Stuepef]

The sheet name is "Plan Rates" or the codename is Sheet3. The cells in column A contain a formula that returns "".

 

[Fluff]

If you have a formula in a cell then that cell is not blank.
Try

Sub HideRws()
   Dim Rng As Range, Cl As Range
   With Sheet3
      For Each Cl In .Range("A11:A60")
         Cl.EntireRow.Hidden = Cl.Value = ""
      Next Cl
      For Each Rng In .Range("A71:A120, A131:A180, A191:A240, A251:A300").Areas
         If Rng(1) = "" Then
            Rng.Offset(-6).Resize(58).EntireRow.Hidden = True
         Else
            For Each Cl In Rng
               Cl.EntireRow.Hidden = Cl.Value = ""
            Next Cl
         End If
      Next Rng
   End With
End Sub

 

[Stuepef]

there we go, THANK YOU FOR THE HELP!!

 

[Fluff]

Glad to help & thanks for the feedback