XLA routines: EE_PivotArrangeRowFields

Nick's picture
Once you have created a pivot table, added the page and col fields, you can now add the row fields with EE_PivotArrangeRowFields
Sub EE_PivotArrangeRowFields(pt As PivotTable, FieldsArrayOrRange)
'- as above but for row fields
    Dim ptRowField  As PivotField
    Dim intFld      As Integer
    Dim arr
'http://excelexperts.com/xla-routines-eePivotArrangeRowFields    for updates on this sub routine

    For Each ptRowField In pt.rowfields
        ptRowField.Orientation = xlHidden
    Next ptRowField
    Select Case TypeName(FieldsArrayOrRange)
        Case "Variant()", "String"
            arr = FieldsArrayOrRange
        Case "Range"
            If FieldsArrayOrRange.Cells.Count = 1 Then
                ReDim arr(0)
                arr(0) = FieldsArrayOrRange
                arr = Application.Transpose(FieldsArrayOrRange)
            End If
        Case Else
    End Select
    For intFld = LBound(arr) To UBound(arr)
        On Error Resume Next
            With pt.PivotFields(arr(intFld))
                .Orientation = xlRowField
                .Position = pt.rowfields.Count
            End With
        Err.Clear: On Error GoTo 0: On Error GoTo -1
    Next intFld
    Erase arr
    Set ptRowField = Nothing
End Sub