Hi all,
I'm new to Excel VBA and first of all, thank you to all of you who so generously give your time to help newbies with problems.
I'm writing code for a shortcut that will toggle between removing or reinserting the '0' pivot item from the row and column fields of an active pivot table. I've encountered 3 problems:
1 - I know best programming practice is not to select objects before manipulating them. But the code I've written seems to only work if the user manually selects the right object [row or column field] within the pivot table. That's not very good, so I've selected the row and column fields in vba. I'd love to know how to get around this.
2 - I've not figured out how to select both row and column fields of a pivot table together. I've tried the union method for the pivot row and column ranges, but it doesn't work. Listing mutliple ranges as arguments to a Range [which is what the Excel macro recorder does when I record manual selections] doesn't work either.
3 - The work around I figured out is to separate removing the '0' from the row and column fields into 2 independent procedures. I tried including these instructions, one after the other, in one sub procedure, but somehow it doesn't work. It does work when I separate them into 2 sub procedures. I thought I'd then use a third sub procedure to call and run both of the needed sub procedures. But that doesn't work and I can't figure out where I've gone wrong.
Here's the code I'm using:
This is one of the sub's for removing '0's from pivot row fields. The one for columns follows this model.
The code I used for running the 2 sub procedures looks like this. I've stored all three sub's in the code for worksheet1 for testing purposes.
Any help or suggestions much appreciated!
I'm new to Excel VBA and first of all, thank you to all of you who so generously give your time to help newbies with problems.
I'm writing code for a shortcut that will toggle between removing or reinserting the '0' pivot item from the row and column fields of an active pivot table. I've encountered 3 problems:
1 - I know best programming practice is not to select objects before manipulating them. But the code I've written seems to only work if the user manually selects the right object [row or column field] within the pivot table. That's not very good, so I've selected the row and column fields in vba. I'd love to know how to get around this.
2 - I've not figured out how to select both row and column fields of a pivot table together. I've tried the union method for the pivot row and column ranges, but it doesn't work. Listing mutliple ranges as arguments to a Range [which is what the Excel macro recorder does when I record manual selections] doesn't work either.
3 - The work around I figured out is to separate removing the '0' from the row and column fields into 2 independent procedures. I tried including these instructions, one after the other, in one sub procedure, but somehow it doesn't work. It does work when I separate them into 2 sub procedures. I thought I'd then use a third sub procedure to call and run both of the needed sub procedures. But that doesn't work and I can't figure out where I've gone wrong.
Here's the code I'm using:
This is one of the sub's for removing '0's from pivot row fields. The one for columns follows this model.
Code:
Sub RemoveZerosFromPivotRow()
Dim PT As PivotTable
Dim PF As PivotField
On Error GoTo NoPivot:
Set PT = ActiveCell.PivotTable
Set PF = ActiveCell.PivotField
PT.RowRange.Select
If PT.RowFields(PF.Name).PivotItems("0").Visible = True Then
PT.RowFields(PF.Name).PivotItems("0").Visible = False
Else
PT.RowFields(PF.Name).PivotItems("0").Visible = True
End If
NoPivot:
Exit Sub
Resume Next
End SubThe code I used for running the 2 sub procedures looks like this. I've stored all three sub's in the code for worksheet1 for testing purposes.
Code:
Sub Test()
Call RemoveZerosFromPivotColumn
Call RemoveZerosFromPivotRow
Application.Run "RemoveZerosFromPivotColumn"
Application.Run "RemoveZerosFromPivotRow"
End SubAny help or suggestions much appreciated!
