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]

Howe about

Sub HideRws()
   Dim Rng As Range
   With Sheets("Sheet3")
      On Error Resume Next
      .Range("A11:A60").SpecialCells(xlBlanks).EntireRow.Hidden = True
      On Error GoTo 0
      For Each Rng In .Range("A71:A120, A131:A180, A191:A240, A251:A300").Areas
         If IsEmpty(Rng(1)) Then
            Rng.Offset(-6).Resize(58).EntireRow.Hidden = True
         Else
            On Error Resume Next
            Rng.SpecialCells(xlBlanks).EntireRow.Hidden = True
            On Error GoTo 0
         End If
      Next Rng
   End With
End Sub

 

[Fluff]

Cross posted https://www.excelforum.com/excel-pr...ased-on-specific-cell-values.html#post4966288

While we do not prohibit Cross-Posting on this site, we do ask that you please mention you are doing so and provide links in each of the threads pointing to the other thread (see rule 13 here along with the explanation: Forum Rules).
This way, other members can see what has already been done in regards to a question, and do not waste time working on a question that may already be answered.

 

[Stuepef]

Apologies on cross posting. When I use the code provided, I receive a a run time error 9 subscript out of range. Sheet3 has a name associated with it and when I update the code to the sheet name, it does not error out, but it does not update anything on the sheet. The ranges you have in the code are the ones I want evaluated, but the second part is the range that needs to be hidden is not the same.

 

[Fluff]

Are you saying that if A71 is blank, no rows get hidden?

 

[Stuepef]

If A71 is blank, then hide range A65:A122. If A71 is not blank, then go through range A71:A120 to gauge if those cells are blank and if they are, then hide them. For instance, if cells A71:A81 are not blank, it would hide A82:A120.

 

[Fluff]

Can you please answer my question?

 

[Stuepef]

No, I'm saying if A71 is blank, hide range A65:A122.

 

[Fluff]

That is exactly what my code does.
If it's not doing that for you, what is it doing?

 

[Stuepef]

I receive a run time error 9 - subscript out of range and the "With Sheets("Sheets3")" is highlighted. Then I changed the ("Sheets3") and the macro ran, but it did not do anything to the ranges. How can this be?