Macro to hide all row values in Pivot Table?

Hello forum,

I would like to create a Macro that would deselect all row values in a Pivot Table and then select only one specific value.

The reason I need this specifically is simple: as each month, new values are added into the excel file and pivot table, I cannot update the Macro each month to deselect a specific value.

This is what I have so far:

With ActiveSheet.PivotTables("PivotTable1").PivotFields("Building ID")
.PivotItems("Building ID").Visible = True
' above is the value I want to make visible
End With

' below are the values I want to hide. These are just five of the approx. 180 values I have to hide. So the Macro itself is unnecessarily long as well
With ActiveSheet.PivotTables("PivotTable5").PivotFields("Building ID")
.PivotItems("Building ID 1").Visible = False
.PivotItems("Building ID 2").Visible = False
.PivotItems("Building ID 3").Visible = False
.PivotItems("Building ID 4").Visible = False
.PivotItems("Building ID 5").Visible = False

Many thanks for helping me out with this one!

Best regards,
Adam

Hi xlnitwit,

many thanks for your response

It indeed works, the only issue I have with this is that when I look at the filter, all row values seem to be selected (eventhough I'm of course only able to see the Building ID value). Why is that?

Additionally, with this method I'm only able to select one value at a time. Let me explain:

For each row value, there is a separate macro. For example, if I want Excel to show me Building ID 2, Building ID 6 and Building ID 12, with this method it's not possible to add another Building ID.

Is there a workaround for that?

Many thanks again!

Cheers,
Adam