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

Question

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

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):

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?

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

Unable to set the Position property of the PivotField class

Any ideas please?

Answer 1

PivotFields has a count property.

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:

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

Answer 2

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

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