Hide indefinite blank rows below pivot tables

Question

Here I go again, another simple Function that I cannot seem to find anywhere. instead, i see other related discussions/codes but they are more complex that how i need it.

I have Three Pivot tables, they are connected via a slicer so when i select one Item, all of them will be filtered in the same parameter.

I would only want to hide the blank rows between a two Pivot table that are blank, excluding 1 rows for each to allow for viewing separation. I tried to use a Recording macro but seems like there's no way i can expect the relative Reference mode to help on this when i use xlUp and xlDown. in the attached image is sample of what are the blank rows that will be hidden.

Thank you so much in advance!

I am really sorry sir i did not mean to shout with that all Caps. Just didn't notice. Thank you for noticing. — BoyDespasito

Armando Franco Armando Franco · Accepted Answer · 2020-11-03T00:34:19

Sub HideBlanks()
    Dim nRow
    ' First, show all rows
    Cells.EntireRow.Hidden = False
    ' Then go from the last row to the third one,
    For nRow = ActiveCell.SpecialCells(xlCellTypeLastCell).Row To 3 Step -1
        ' check that several columns of that row are blank (Cells(nRow, 1) = "" And Cells(nRow, 2) = "" And Cells(nRow, 3) = "" )
        ' and also check that the row above is blank also (And Cells(nRow - 1, 1) )
        If Cells(nRow, 1) = "" And Cells(nRow, 2) = "" And Cells(nRow, 3) = "" And Cells(nRow - 1, 1) = "" Then
            ' If so, hide the row
            Cells(nRow, 1).EntireRow.Hidden = True
        End If
    Next nRow
End Sub

Hide indefinite blank rows below pivot tables

1 Answers