VBA sort pivot tables and hide blanks

[kcmuppet]

Hello,

I'm trying to sort the pivot tables on my sheet and hide the blank entries in the row field

My code so far seems to sort but fail on the pf.PivotItems("").Visible = False part.

Sub SortPivots_HideBlanks()
    Dim pt As PivotTable
    Dim pf As PivotField
    For Each pt In ActiveSheet.PivotTables
        For Each pf In pt.RowFields
            pf.PivotItems.AutoSort xlDescending
            pf.PivotItems("").Visible = False
        Next pf
    Next pt
End Sub

What am I doing wrong?

 

[RoryA]

IIRC you actually have to use the word: pf.PivotItems("(blank)").Visible = False

By the way, I am still pondering your other question - I had forgotten how difficult olap pivot tables can be in VBA!

 

[kcmuppet]

IIRC you actually have to use the word: pf.PivotItems("(blank)").Visible = False

Thanks for your reply. Both pf.PivotItems("(blank)") and pf.PivotItems("") give me Run-time error '438'. Object doesn't support this property or method.

By the way, I am still pondering your other question - I had forgotten how difficult olap pivot tables can be in VBA!

Thanks, I'm still struggling with it

 

[kcmuppet]

Trial and error got me to this , which seems to work:

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("").Visible = False
        End With
        Next pf
    Next pt
End Sub

 

[RoryA]

I think your 438 error was due to the previous line applying autosort to PivotItems

 

[smozgur]

About the error: it is the AutoSort method which is not a method of the PivotItems collection as explained by @RoryA, and that you already noticed and fixed in your last post.

However, .PivotItems("") is not a valid item as an empty string is not a valid reference so this line is supposed to generate an error unless you have an On Error statement. So, your final code won't work but fail.

The correct implementation is .PivotItems("(blank)").Visible = False as @RoryA explained. That's why I switched the solution post for this question to point to the correct implementation to help future readers.

 

[kcmuppet]

Thanks for the explanation

 

[kcmuppet]

Well I guess I spoke too soon. Today I'm getting errors with exactly the same code with some updated underlying data

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'm obviously going about this the wrong way. What is the right way to do this properly?

(I'm trying to get every pivot table on the active sheet to have its rows sorted in descending order, and filter out blanks)

Analyses of Sample data v0-22.xlsm
	N	O	P	Q	R	S
21		Item 1	Item 2	Item 3	Item 4	Overall
22	(blank)		0%	0%	0%	0%
23	5		40%	42%	50%	42%
24	4		20%	8%	0%	11%
25	3		40%	17%	50%	26%
26	2		0%	17%	0%	11%
27	1		0%	17%	0%	11%
Details

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
Q22:Q27	Other Type	DataBar		NO
R22:R27,S22:S27	Other Type	DataBar		NO
O22:P27	Other Type	DataBar		NO

(Column N is PivotTable RowField entries, Row 21 has PivotTable column headings but omitted here for confidentiality of the patients)

 

[smozgur]

Could you please try the following code?
* Change the "ID" string with the name of the row field. Please note it is used twice in the code below.

Sub SortPivots_HideBlanks()
    Dim pt As PivotTable
    For Each pt In ActiveSheet.PivotTables
        With pt.PivotFields("ID")
            .AutoSort xlDescending, "ID"
            .PivotItems("(blank)").Visible = False
        End With
    Next pt
End Sub

Also, how come the "Item 1" pivot table column has no data?

 

[kcmuppet]

Could you please try the following code?
* Change the "ID" string with the name of the row field. Please note it is used twice in the code below.

Sub SortPivots_HideBlanks()
    Dim pt As PivotTable
    For Each pt In ActiveSheet.PivotTables
        With pt.PivotFields("ID")
            .AutoSort xlDescending, "ID"
            .PivotItems("(blank)").Visible = False
        End With
    Next pt
End Sub

Thanks, unfortunately the row field name is different in each of the 100 or so pivot tables on the sheet, which is why I was trying to wrap another loop into it for the row field.

If I pick one of the tables and enter the name in the in place of ID in the code you suggested I get error 1004.

Some of the field names have these characters in them: $ ? . - in them, like "Check $First-Results on time?", but I'm assuming if the name is between the " " it doesn't matter?

Also, how come the "Item 1" pivot table column has no data?

In the example I pasted there was a slicer applied which filtered out results for that item.