• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User


  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Sort pivot table row fields and remove blanks for all pivot tables on a sheet



I'm trying to sort the rows on all pivot tables on a sheet and hide blanks

I've tried:
Sub SortPivots_HideBlanks()
    Dim pt As PivotTable
    Dim pf As PivotField
    For Each pt In ActiveSheet.PivotTables
        For Each pf In pt.RowFields
        With pf
            .AutoSort xlDescending, pf
            .PivotItems("(blank)").Visible = False
        End With
        Next pf
    Next pt
End Sub
Which seemed to work once but not again:
At .AutoSort xlDescending, pf I get 1004 Application-defined or object defined error. If I comment that out...
at .PivotItems("(blank)").Visible = False I get 1004 Unable to get the PivotItems property of the PivotField class

I managed to get the following to work to hide the blanks, but it's inefficient/ runs slowly with 100 or so tables and stops with an error when it can't find anymore blank rowfields.

Sub SortPivots_HideBlanks5()
        Dim pt As PivotTable
        Dim pf As PivotField
        Dim pi As PivotItem   
    For Each pt In ActiveSheet.PivotTables
        For i = 1 To pt.RowFields.Count
          For Each pi In pt.RowFields(i).PivotItems
              If pi.Name = "(blank)" Then
                    pi.Visible = False
                    Exit For
                End If
            Next pi
        Next i
'       pf.AutoSort xlDescending, .Name <--This is what I tried for sorting, but it fails to compile "invalid or unqualified reference"
    Next pt
End Sub

I've posted this question here already, but despite the efforts of a couple of kind people, I'm floundering. Would greatly appreciate some help from members of this forum too.