Excel VBA - How to determine the number of row label fields in a pivot table?

Question

I have an Excel 2010 pivot table containing, at first, three row label fields.

I need to create a macro that will add a specific field as the label field of the bottom row in the pivot table. (For reasons that I will not enter, the user cannot add this field on his own.)

However, by the time the user starts this macro, they can add or remove some line labels.

This macro recorder gives me this when I add a field to the lowest position of the 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 deleted some elements, this position No. 4 is incorrect. How do I pass the correct position number into my code?

Trying to aim 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 

Alternatively, instead of PivotFields you can replace other parameters to give you other values:

• Column fields
• Data fields
• Hiddenfields
• PageFields
• Rowfields
• Visible fields

Answer 2

Try to completely remove the line .Position = 4 , i.e.:

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

Answer 3

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 

Answer 4

If the task is to count the number of row fields that may exist in the pivot table, you should use this line of code:

 pt.RowRange.cells.count 

This will give the number of turns. However, this will count the header row as well as the overall row (if the totals apply to the pivot table). Thus, you will need to subtract 1 or 2 depending on your situation.

Here you can find a great description of how to access ranges of values.