Excel VBA - 如何确定数据透视表中的行标签字段数？

Excel VBA - How can I determine number of row label fields in Pivot Table?

提问by Andi Mohr

I have an Excel 2010 pivot table containing, initially, 3 row label fields.

我有一个 Excel 2010 数据透视表，最初包含 3 个行标签字段。

I need to create a macro that will add a certain field as the lowest row label field in the pivot table. (For reasons I won't go into, the user can't add this field in the normal way themselves.)

我需要创建一个宏，将某个字段添加为数据透视表中的最低行标签字段。（出于我不会讨论的原因，用户自己无法以正常方式添加此字段。）

However, by the time the user runs this macro, they may have added or removed some row labels.

然而，当用户运行这个宏时，他们可能已经添加或删除了一些行标签。

The macro recorder gives me this when I add a field in the lowest position of a pivot table (with 3 row labels already selected):

当我在数据透视表的最低位置添加一个字段（已经选择了 3 个行标签）时，宏记录器给了我这个：

With ActiveSheet.PivotTables("MyPivotTable").PivotFields("MyNewField")
    .Orientation = xlRowField
    .Position = 4
End With

If the user has added or removed some items, this position number 4 is incorrect. How would I pass into my code the correct position number?

如果用户添加或删除了某些项目，则此位置编号 4 是不正确的。我如何将正确的位置编号传递到我的代码中？

Trying to aim high using Position = 99gives me the following error:

试图瞄准高使用Position = 99给了我以下错误：

Unable to set the Position property of the PivotField class

无法设置 PivotField 类的 Position 属性

Any ideas please?

请问有什么想法吗？

回答by SeanC

PivotFieldshas a count property.

PivotFields有一个计数属性。

With ActiveSheet.PivotTables("MyPivotTable").PivotFields("MyNewField")
    .Orientation = xlRowField
    .Position = ActiveSheet.PivotTables("MyPivotTable").PivotFields.Count + 1
End With

Also, instead of PivotFields, you can substitute other selections to give you other counts:

此外，除了 ，PivotFields您可以替换其他选择来为您提供其他计数：

• ColumnFields
• DataFields
• HiddenFields
• PageFields
• RowFields
• VisibleFields

• 列字段
• 数据字段
• 隐藏字段
• 页面字段
• 行字段
• 可见字段

回答by Peter L.

Try to throw away the .Position = 4string entirely, i.e.:

尝试.Position = 4完全扔掉字符串，即：

With ActiveSheet.PivotTables("MyPivotTable").PivotFields("MyNewField")
    .Orientation = xlRowField
End With

回答by Axel Kemper

A brute-force solution:

一个蛮力解决方案：

Public Function GetPivotFieldPos(pt As PivotTable, fieldName As String)
    Dim cnt As Integer
    Dim pos As Integer
    Dim pf As PivotField

    On Error GoTo ErrHandler

    cnt = 0   '  might be 1-based!?
    pos = -1
    For Each pf In pt.PivotFields
        cnt = cnt + 1
        If pf.Name = fieldName Then
            pos = cnt
            Exit For
        End If
    Next

    GetPivotFieldPos = pos
    Exit Function

ErrHandler:
    GetPivotFieldPos = -1
    On Error GoTo 0
End Function

回答by timbur

If the task at hand is to count the number of rowfields that may exist in a pivottable, you should enjoy this line of code:

如果手头的任务是计算数据透视表中可能存在的行字段数，您应该喜欢这行代码：

pt.RowRange.cells.count

This will give the count of the pivotrows. However, this will count the heading row as well as the total row (if totals are applied to the pivottable.) So, you will need to subtract 1 or 2 depending on your situation.

这将给出枢轴行的计数。但是，这将计算标题行和总行（如果总计应用于数据透视表）。因此，您需要根据您的情况减去 1 或 2。

An excelent description of how to refer to pivottable ranges can be found here.

可以在此处找到有关如何引用数据透视表范围的出色描述。