VBA code to filter out blank values in a pivot table

[dragontbone]

I am attempting to have VBA loop through all of the sheets in my workbook and find a pivot table from several of those sheets located in the same spot for each one. I need it to filter out each instance of (blank) and "" values. However, the code is not filtering out the blanks.

Broker 1
Year
Quarter
Row Labels
(blank)

I can't show any more of this pivot table since it has to do with some sensitive info, but any help would be fantastic.

Sub RefreshAllPivotTables()
    Application.Calculation = xlAutomatic
    Dim ws As Worksheet
    Dim pt As PivotTable
    Dim pf As PivotField
    Dim pi As PivotItem
    Application.DisplayAlerts = False
    
    PleaseWait
    DoEvents
    Application.ScreenUpdating = False 
    cleanMasterTable
    
    ' Loop through each worksheet in the workbook
    For Each ws In ThisWorkbook.Worksheets
        ws.Activate 
        
        ' Check if the second word in the sheet name is "Summary"
        Dim sheetNameParts() As String
        sheetNameParts = Split(ws.Name, " ")
        
        If UBound(sheetNameParts) >= 1 Then
            If sheetNameParts(1) = "Summary" Then
                ' Loop through each PivotTable in the worksheet
                For Each pt In ws.PivotTables
                    pt.RefreshTable
                    
                    ' Apply filters and sort if pivot table is in columns G and H starting from row 72
                    If Not Intersect(pt.TableRange2, ws.Range("G72:H" & ws.Rows.Count)) Is Nothing Then
                        On Error Resume Next
                        Set pf = pt.PivotFields("Broker Pd")
                        On Error GoTo 0
                        
                        If Not pf Is Nothing Then
                            With pf
                                .ClearAllFilters
                                On Error Resume Next
                                ' Ensure the "(blank)" item is hidden
                                If .PivotItems("(blank)").Visible Then
                                    .PivotItems("(blank)").Visible = False
                                End If
                                
                                ' Hide any empty or whitespace values
                                For Each pi In .PivotItems
                                    If Trim(pi.Value) = "" Then
                                        pi.Visible = False
                                    End If
                                Next pi
                                On Error GoTo 0
                                
                                ' Sort the items
                                .AutoSort xlDescending, "Broker Pd"
                            End With
                        End If
                    End If
                Next pt
            End If
        End If
    Next ws
    
    HidePleaseWait

    Worksheets("OBK Summary Detail").Activate
    Columns("A:H").Select
    Selection.Columns.AutoFit
    
    Application.ScreenUpdating = True 
    Application.DisplayAlerts = True
End Sub

 

[DanteAmor]

Try this:

Sub RefreshAllPivotTables()
    Dim ws As Worksheet
    Dim pt As PivotTable
    Dim pf As PivotField
    Dim pi As PivotItem
    Dim sheetNameParts() As String
    
    Application.Calculation = xlAutomatic
    Application.DisplayAlerts = False
    Application.ScreenUpdating = False
    
    Call PleaseWait
    DoEvents
    Call cleanMasterTable
    
    ' Loop through each worksheet in the workbook
    For Each ws In ThisWorkbook.Worksheets
        ws.Activate
        
        ' Check if the second word in the sheet name is "Summary"
        sheetNameParts = Split(ws.Name, " ")
        
        If UBound(sheetNameParts) >= 1 Then
            If sheetNameParts(1) = "Summary" Then
                ' Loop through each PivotTable in the worksheet
                For Each pt In ws.PivotTables
                    pt.RefreshTable
                    
                    ' Apply filters and sort if pivot table is in columns G and H starting from row 72
                    If Not Intersect(pt.TableRange2, ws.Range("G72:H" & ws.Rows.Count)) Is Nothing Then
                    
                      With pt.PivotFields("Broker Pd")
                          .ClearAllFilters
                          .PivotItems("(blank)").Visible = False
                          ' Sort the items
                          .AutoSort xlDescending, "Broker Pd"
                      End With

                    End If
                Next pt
            End If
        End If
    Next ws
    
    Call HidePleaseWait

    Worksheets("OBK Summary Detail").Activate
    Columns("A:H").Select
    Selection.Columns.AutoFit
    
    Application.ScreenUpdating = True
    Application.DisplayAlerts = True
End Sub

 

[dragontbone]

Try this:

Sub RefreshAllPivotTables()
    Dim ws As Worksheet
    Dim pt As PivotTable
    Dim pf As PivotField
    Dim pi As PivotItem
    Dim sheetNameParts() As String
   
    Application.Calculation = xlAutomatic
    Application.DisplayAlerts = False
    Application.ScreenUpdating = False
   
    Call PleaseWait
    DoEvents
    Call cleanMasterTable
   
    ' Loop through each worksheet in the workbook
    For Each ws In ThisWorkbook.Worksheets
        ws.Activate
       
        ' Check if the second word in the sheet name is "Summary"
        sheetNameParts = Split(ws.Name, " ")
       
        If UBound(sheetNameParts) >= 1 Then
            If sheetNameParts(1) = "Summary" Then
                ' Loop through each PivotTable in the worksheet
                For Each pt In ws.PivotTables
                    pt.RefreshTable
                   
                    ' Apply filters and sort if pivot table is in columns G and H starting from row 72
                    If Not Intersect(pt.TableRange2, ws.Range("G72:H" & ws.Rows.Count)) Is Nothing Then
                   
                      With pt.PivotFields("Broker Pd")
                          .ClearAllFilters
                          .PivotItems("(blank)").Visible = False
                          ' Sort the items
                          .AutoSort xlDescending, "Broker Pd"
                      End With

                    End If
                Next pt
            End If
        End If
    Next ws
   
    Call HidePleaseWait

    Worksheets("OBK Summary Detail").Activate
    Columns("A:H").Select
    Selection.Columns.AutoFit
   
    Application.ScreenUpdating = True
    Application.DisplayAlerts = True
End Sub

Unfortunately, that did not work. I am not sure if this is relevant or not, but the Broker Pd field is in the Rows section of the pivot table, and not the filters section.