Remove Blanks from Pivotable Group

Question

I have an Excel dataset to be pivoted with row labels grouped by months (row labels are dates).

I have the VBA setup to do the pivot and then group the row labels to months.

After it groups to months, I'm trying to get the (blank) field in the dropdown to deselect.

I tried recording a macro; it just did it like "<11/15/15".

I found code, but I get the error message

pivot item cannot be found.

Dim p_i As PivotItem

With ActiveSheet.PivotTables("PivotTable1").PivotFields("Delivery Time of Actual Item")
    .PivotItems("(blank)").Visible = True
    For Each p_i In .PivotItems
        If p_i.Name = "(blank)" Then
            p_i.Visible = False
        End If
    Next
End With

Could the issue be that I group the items by month first?

Answer 1

My solution might no be the most elegant, but you have to change Visible value of your pivot item to TRUE. Then refresh the pivot (in case automatic workbook calculation is turned off) and then you can freely change Visible value to FALSE:

With ActiveSheet.PivotTables("PivotTable1")
    .PivotFields("Delivery Time of Actual Item").PivotItems("(blank)").Visible = True
    .RefreshTable
    .PivotFields("Delivery Time of Actual Item").PivotItems("(blank)").Visible = False
End With

I have also an alternative solution (even less elegant): you can remove blank values from a pivot with a date filter:

ActiveSheet.PivotTables("PivotTable1").PivotFields("Delivery Time of Actual Item")
.PivotFilters.Add2 Type:=xlAfter, Value1:="1901-01-01"

This could also be done with a label filter (in case you had a string/number table):

ActiveSheet.PivotTables("PivotTable1").PivotFields("Delivery Time of Actual Item")
    .PivotFilters.Add2 Type:=xlCaptionIsGreaterThan, Value1:="0"

Hope that helps.

Answer 2

If you still want to count values of the "blank", you may want to try this code:

Dim p_i As PivotItem

With ActiveSheet.PivotTables("PivotTable1").PivotFields("Delivery Time of Actual Item")
    .PivotItems("(blank)").Visible = True
    For Each p_i In .PivotItems
        If p_i.Name = "(blank)" Then
            p_i.NumberFormat = ";;;"
        End If
    Next
End With

Answer 3

On Error Resume Next
    Dim ip As Long
    Dim it As Long
    For ip = 1 To ActiveSheet.PivotTables.Count
        Dim pt As PivotTable
        Set pt = ActiveSheet.PivotTables(ip)
        pt.PivotCache.Refresh
        pt.ClearAllFilters
        pt.RefreshTable

        Dim pf As PivotField
        For it = 1 To pt.PivotFields.Count
            Set pf = pt.PivotFields(it)

            Dim pi As PivotItem
            For Each pi In pf.PivotItems
                If pi.Name = "(blank)" Or pi.Name = "#N/A" Then
                    pi.Visible = False
                End If
            Next pi
        Next it
    Next ip

Answer 4

I think you can get your answer if you turn on the Macro recorder and click through the steps with your mouse. Try it and see if it gives you what you want.