从数据透视组中删除空格 [英] Remove Blanks from Pivotable Group

问题描述

我有一个Excel数据集，可以通过按月分组的行标签(行标签是日期)进行透视.

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

我有VBA设置来执行数据透视，然后将行标签分组为几个月.

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.

我尝试录制宏；就像< 11/15/15"一样.

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?

推荐答案

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

这篇关于从数据透视组中删除空格的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！