CODEWITHSUNDEEP
excelvbapivot-table
BoyDespasito
BoyDespasito

Reputation: 21

Hide indefinite blank rows below pivot tables

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!

enter image description here

Upvotes: 0

Views: 423

Answers (1)

Armando Franco
Armando Franco

Reputation: 61

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

Upvotes: 1

Related Questions