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

[TABLE="width: 682"]
<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>[TR]
[TD="width: 305"]Count of Status[/TD]
[TD="width: 129"]Column Labels[/TD]
[TD="width: 66"] [/TD]
[TD="width: 27"] [/TD]
[TD="width: 96"] [/TD]
[TD="width: 93"] [/TD]
[TD="width: 101"] [/TD]
[TD="width: 91"] [/TD]
[/TR]
[TR]
[TD]Row Labels[/TD]
[TD] [/TD]
[TD]> 5 Days[/TD]
[TD]NA[/TD]
[TD]Uncorrected[/TD]
[TD]Within 1 Day[/TD]
[TD]Within 5 Days[/TD]
[TD]Grand Total[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]Claims - Credit & Surety Claims[/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"]1[/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"]1[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]Claims - Long Tail[/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"]2[/TD]
[TD="bgcolor: transparent"]2[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]Claims - Short Tail[/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"]6[/TD]
[TD="bgcolor: transparent"]1[/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"]7[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]Claims - Workers Compensation - Claims[/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"]1[/TD]
[TD="bgcolor: transparent"]1[/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"]3[/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"]5[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]Policy Support Services - Credit Control[/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"]1[/TD]
[TD="bgcolor: transparent"]4[/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"]6[/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"]11[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]Policy Support Services - Elders[/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"]2[/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"]4[/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"]6[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]Policy Support Services - Financial Institution[/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"]2[/TD]
[TD="bgcolor: transparent"]3[/TD]
[TD="bgcolor: transparent"]5[/TD]
[TD="bgcolor: transparent"]1[/TD]
[TD="bgcolor: transparent"]11[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]Policy Support Services - Motorcycle[/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"]4[/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"]4[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"](blank)[/TD]
[TD="bgcolor: transparent"]113[/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"]113[/TD]
[/TR]
[TR]
[TD]Grand Total[/TD]
[TD]113[/TD]
[TD]2[/TD]
[TD]19[/TD]
[TD]4[/TD]
[TD]19[/TD]
[TD]3[/TD]
[TD]160[/TD]
[/TR]
</tbody>[/TABLE]

 

[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.