Auto Hide/Show rows based on multiple data VBA using Worksheet Activate

[Sunshine8790]

Hi all,
Newbie here, just learning about macros and VBAs.
I have a workbook with 3 worksheets in it:
1. Hub (where all my macro enabled buttons are, to pull, edit and delete data on the other sheets)
2. Data sheet (where my data is imported from a separate workbook)
3. Pivot sheet - this is the sheet I'm having difficulty with.

On the pivot sheet, depending on the data imported, sometimes there are blank rows in one of the multiple pivots that say: (blank).
What I am wanting to do is have the sheet automatically hide any rows between a certain set of columns and rows that contain either that term "(blank)" or no value at all.
So, in my example below, I need all the rows with (blank) hidden - and I need all the rows between rows 5 and 28 that contain no data at all (as seen in rows 14-17 - I have 0's in the yellow parts in yellow text to avoid having the yellow hidden) to be hidden.
But since this data will change every time new data is imported to my other tab, I need it to automatically unhide those cells if data populates those same cells.

Does that make sense?

So right now, I have a VBA code inserted where you right click on the worksheet itself and hit "view code" that IS working to remove just the cells with no data at all - but I need to also have it remove the (blank) text rows:

Private Sub Worksheet_Activate()

Dim rCheck As Range
Dim rHide As Range
Dim rCheckCell As Range

Set rCheck = ActiveWorkbook.ActiveSheet.Range("B3:B30")
rCheck.EntireRow.Hidden = False

For Each rCheckCell In rCheck.Cells
If InStr(1, rCheckCell, "(blank)", vbTextCompare) > 0 Then
If Not rHide Is Nothing Then Set rHide = Union(rHide, rCheckCell) Else Set rHide = rCheckCell
End If
Next rCheckCell

If Not rHide Is Nothing Then rHide.EntireRow.Hidden = True
stCol = 1
endCol = 10
stRow = 5
endRow = 30
For r = stRow To endRow
counter = 0
For c = stCol To endCol
If Cells(r, c).Value = "" Then
counter = counter + 1
End If
Next c
If counter = endCol Then
Rows(r).EntireRow.Hidden = True
Else
Rows(r).EntireRow.Hidden = False
End If
Next r
End Sub

 

[Sunshine8790]

Solved my own question using the below VBA code:

Private Sub Worksheet_Activate()
Dim r As Long
Dim m As Long
Application.ScreenUpdating = False
Me.Range("B5:B30").EntireRow.Hidden = False
m = Range("B" & Me.Rows.Count).End(xlUp).Row
For r = 1 To m
If Range("B" & r).Value = "" Or Range("B" & r).Value = "(blank)" Then
Range("B" & r).EntireRow.Hidden = True
End If
Next r
Application.ScreenUpdating = True
End Sub

Private Sub Worksheet_Activate()
    Dim r As Long
    Dim m As Long
    Application.ScreenUpdating = False
    Me.Range("B5:B30").EntireRow.Hidden = False
    m = Range("B" & Me.Rows.Count).End(xlUp).Row
    For r = 1 To m
        If Range("B" & r).Value = "" Or Range("B" & r).Value = "(blank)" Then
            Range("B" & r).EntireRow.Hidden = True
        End If
    Next r
    Application.ScreenUpdating = True
End Sub