Hello,
I'm trying to sort the rows on all pivot tables on a sheet and hide blanks
I've tried:
Which seemed to work once but not again:
At
at
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.
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.
I'm trying to sort the rows on all pivot tables on a sheet and hide blanks
I've tried:
Code:
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 classI 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.
Code:
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.