Remove Blanks from Pivot Table VBA

[Bryan123]

Hi All,

How do you remove a row with "(blank)" from a pivot table which is also producing count for blanks? VBA

Count of Status	Column Labels
Row Labels		> 5 Days	NA	Uncorrected	Within 1 Day	Within 5 Days	Grand Total
Claims - Credit & Surety Claims					1		1
Claims - Long Tail						2	2
Claims - Short Tail			6	1			7
Claims - Workers Compensation - Claims		1	1		3		5
Policy Support Services - Credit Control		1	4		6		11
Policy Support Services - Elders			2		4		6
Policy Support Services - Financial Institution			2	3	5	1	11
Policy Support Services - Motorcycle			4				4
(blank)	113						113
Grand Total	113	2	19	4	19	3	160

<colgroup><col width="305" style="width: 229pt; mso-width-source: userset; mso-width-alt: 9760;"> <col width="129" style="width: 97pt; mso-width-source: userset; mso-width-alt: 4128;"> <col width="66" style="width: 50pt; mso-width-source: userset; mso-width-alt: 2112;"> <col width="27" style="width: 20pt; mso-width-source: userset; mso-width-alt: 864;"> <col width="96" style="width: 72pt; mso-width-source: userset; mso-width-alt: 3072;"> <col width="93" style="width: 70pt; mso-width-source: userset; mso-width-alt: 2976;"> <col width="101" style="width: 76pt; mso-width-source: userset; mso-width-alt: 3232;"> <col width="91" style="width: 68pt; mso-width-source: userset; mso-width-alt: 2912;"> <tbody> </tbody>

 

[JustynaMK]

Hi Bryan,

Try this:

    With ActiveSheet.PivotTables([COLOR=#ff0000]"PivotTable1"[/COLOR]).PivotFields([COLOR=#0000ff]"data"[/COLOR])
        .PivotItems("(blank)").Visible = False
    End With

You need to change Pivot Table name (red font) and column name where all your "Row Labels" reside (blue font).
You can also try to record the macro and remove "(blank)" from Row Labels - this should guide you in the right direction.

Hope it helps.

 

[Bryan123]

Hey, works like a miracle. THanks!

 

[JustynaMK]

No problem! Take care.