Remove Blanks from Pivotable Group

1
votes

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?

4
excelvbapivot-table
if you delete this line .PivotItems("(blank)").Visible = True does it work? – Scott Holtzman
That did not work. I have finally just decided to do a pre-format of the data before the pivot and just delete rows that had blank data. This way blanks are not created at all since I just filter them out anyway. I would still like to see if someone knows why this is not working though. – Zionmoose
I just tested this code as is and cannot reproduce the problem. I then removed all blank rows from my data and tested it again and it failed on the .PivotItems("(blank")).Visible = True line, but did not fail on the For Loop. I hope this testing helps you some way. – Scott Holtzman
I ended up fixing my issue by just removing all items that would produce blanks anyway. Except this really doesnt answer my original question, so I will leave it open. – Zionmoose

4 Answers

0
votes

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.

0
votes

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
0
votes
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
-2
votes

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.