Hiding Row Fields pivot Table VBA 400 error

teatimecrumpet

Active Member
Joined
Jun 23, 2010
Messages
307
Hi,

I've used some code with the macro recorder to hide certain elements of a particular row field. But when trying to use it on a similar set of data it will return a 400 error when it tries to hide a field that is not there.

Is there a way to change the code so it will hide if it IS there but ignore trying to hide it if it IS NOT there and continue to hide the others?

here is the code:

With ActiveSheet.PivotTables("TD").PivotFields( _
"code")
.PivotItems("DL").Visible = False
.PivotItems("FD").Visible = False
.PivotItems("NF").Visible = False
.PivotItems("NP").Visible = False
.PivotItems("PE").Visible = False
.PivotItems("PR").Visible = False
.PivotItems("SC").Visible = False
.PivotItems("TC").Visible = False
.PivotItems("(blank)").Visible = False
End With

it'll crash or return the 400 error when it tries to hide "NF" which I've highlighted red above.


Thanks in advance,
Mike
 

Some videos you may like

Excel Facts

Wildcard in VLOOKUP
Use =VLOOKUP("Apple*" to find apple, Apple, or applesauce

AlphaFrog

MrExcel MVP
Joined
Sep 2, 2009
Messages
16,384
Try something like this...

Code:
[COLOR="Red"]On Error Resume Next[/COLOR]
With ActiveSheet.PivotTables("TD").PivotFields("code")
.PivotItems("DL").Visible = False
.PivotItems("FD").Visible = False
.PivotItems("NF").Visible = False
.PivotItems("NP").Visible = False
.PivotItems("PE").Visible = False
.PivotItems("PR").Visible = False
.PivotItems("SC").Visible = False
.PivotItems("TC").Visible = False
.PivotItems("(blank)").Visible = False
End With
[COLOR="Red"]On Error Goto 0[/COLOR]
 

teatimecrumpet

Active Member
Joined
Jun 23, 2010
Messages
307
Thanks AlphaFrog!

Worked great.


bonus question:
I'm always hiding all others except one. Is there an alternative way or more efficient way to have this task performed instead of writing out each individual instance of what should be hidden?
 

Watch MrExcel Video

Forum statistics

Threads
1,123,369
Messages
5,601,223
Members
414,434
Latest member
Riyen

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top