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

Answer 3

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

Answer 4

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.

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