Removing (blanks) from all Pivot Tables in Worksheet

Question

I have 5+ Pivot Tables in a Worksheet where some cells have an empty source cell and are displayed with the typical (blank). I want the pivot tables to display an empty cell instead of the word (blank).

I have reviewed other solutions but they are asking to have empty rows removed or the table filtered etc.

Closest thing I found was under Excel VBA Remove Blanks from Pivotable Group

but in this solution a single column is identified and I need this to be applied to all pivot tables.

Tried just simple recording the macro steps but am getting a Run time error 1004 on the ExecutExcel4Macro line when I try to re-run the macro.

Sub HB_Erase_Blank()
'
' HB_Erase_Blank Macro

   Range("D4").Select
   Range("D4:CA6699").Select

   Selection.NumberFormat = ";;;"
   Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlEqual, _
    Formula1:="=""(blank)"""
   Selection.FormatConditions(Selection.FormatConditions.Count).
   SetFirstPriority
   ExecuteExcel4Macro "(2,1,"";;;"")"
   Selection.FormatConditions(1).StopIfTrue = False
End Sub

Is the (blank) you're trying to eliminate appearing in the values or within the Rows/Columns? — Mark S.

Mark S. Mark S. · Accepted Answer · 2019-08-13T16:18:58

in the actual Fields (Rows/Columns) as opposed to Values if you want to have (blank) show up as an empty cell the secret is in conditional formatting.

Highlight the Pivot table you want formatted
Under the Home tab in the ribbon select Conditional Formatting
Select New Rule
Select Format only cells that contain
Change between to equal to
Type into the input box (blank) exactly.
Change the fill format to suit the background of your pivot table (ie: white background, fill with white). This will give the appearance of an empty cell when the word (blank) appears.

To do this through VBA you can use the following code. It may not be perfect in how it's done, but it should work fine. It finds every pivot table in everysheet and looks for (blank) and conditionally formats it to be white (the background and font) change that color if you want something else.

Sub HideBlank()
Dim Pivot As PivotTable
Dim sh As Worksheet

For Each sh In ThisWorkbook.Worksheets
    For Each Pivot In sh.PivotTables
     Pivot.TableRange1.FormatConditions.Add Type:=xlCellValue, Operator:=xlEqual, _
        Formula1:="=""(blank)"""
        With Pivot.TableRange1.FormatConditions(1).Font
            .ThemeColor = xlThemeColorDark1
            .TintAndShade = 0
        End With
        With Pivot.TableRange1.FormatConditions(1).Interior
            .PatternColorIndex = xlAutomatic
            .ThemeColor = xlThemeColorDark1
            .TintAndShade = 0
        End With
    Next
Next

End Sub

Removing (blanks) from all Pivot Tables in Worksheet

1 Answers