Recorded a macro to change (blank) in pivot table to an empty cell. I get a 1004 error when running-CodePudding

I have a pivot table with a lot of (blank) in cells and I would rather them be just blank. I recorded a macro to hide this expression using conditional formatting. When I do it manually, it works but when I run the recorded macro it gets a 1004 error when it gets to the ExecuteExcel4Macro command. Here's my recorded code with no edits:

    Range("H5:M17").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

Here's the error:

I also think the second command (Selection.NumberFormat = ";;;") is superfluous, but left it in for now. Any help appreciated!

CodePudding user response：

Have you looked into PivotTable Options? You might be able to avoid conditional formatting. Just look for the "For empty cells show:" box.

CodePudding user response：

Bryan had the answer. Here's the working code:

    Range("H5:M17").Select
    Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlEqual, _
        Formula1:="=""(blank)"""
    Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
    Selection.FormatConditions(1).NumberFormat = ";;;"
    Selection.FormatConditions(1).StopIfTrue = False