Results 1 to 2 of 2
2010-05-11, 22:21 #1
- Join Date
- May 2010
- Sydney Australia
- Thanked 0 Times in 0 Posts
I'm having some problems with grouping pivottable items within a macro (Excel 2007 VBA), when some of the data is blank. Any help would be appreciated.
One of the fields in my pivot table is called "rson". It can contain a variety of codes. I need to put them into different groups - which is easy enough when each one has data, but sometimes they don't, causing my macro to stop with a run-time error. For illustration, here are my codes, and the groups that I want them in (done as a list rather than a table - forum's table formatting is messy)
(There are more, but that's enough for illustration)
Group ("rson2") --- "rson" codes to put into group
Voluntary Redundancy --- TVR, VGR, VRS, VSS
Forced Separation --- ABN, RCT, TOE
Agreed Period Expired --- END, SEV
In any particular data set, not all of the "rson" codes will be present. Sometimes, an entire group won't be present - for example, if there were no voluntary redundancies during a reporting period then none
of the codes TVR, VGR, VRS, VSS will be present in the data.
At the moment, the way I've been doing it is, for each group:
- Make each row visible if it's part of that list
- (All the other rows are invisible)
- Group the visible rows using PivotSelect
- Set the name of the group that's been created
This works fine when there is at least one record in each group. But if one of the groups is not present, then I get a run-time error because Excel won't let all the pivotitems be made invisible - just like if I was working with the PivotTable manually, if I click on the "rson" dropdown to select values, the "OK" button is greyed out if none of the item codes are selected.
The code I'm using at the moment:
' this is an example of the calling code. SourcePivot is the relevant PivotTable, CodeList is a Variant array) ... CodeList = Array("TVR", "VGR", "VRS", "VSS") GroupListOfItems SourcePivot, "rson", "Voluntary redundancy", CodeList ... Sub GroupListOfItems(PT As PivotTable, BaseField As String, GroupLabel As String, ItemLabels() As Variant) Dim PI As PivotItem Dim rng As Range Dim GroupField As String GroupField = BaseField & "2" With PT.PivotFields(BaseField) .EnableMultiplePageItems = True .Orientation = xlRowField For Each PI In .PivotItems ' IsMemberOf checks to see if the first parameter (string) is present in the second parameter (variant array) PI.Visible = IsMemberOf(PI.Name, ItemLabels) ' <---- code fails here, ONLY where there is only one pivotitem still visible and we're trying to make it invisible Next End With PT.PivotSelect BaseField, xlDataAndLabel Set rng = Selection rng.Group For Each PI In PT.PivotFields(GroupField).PivotItems If Left(PI.Name, 5) = "Group" Then ' Messy, but don't know a better way to do it PI.Name = GroupLabel Exit For End If Next With PT.PivotFields(GroupField) .ClearAllFilters .Orientation = xlPageField End With End Sub
I've already tried and ruled out:
- Using a PivotTable Calculated Field or Calculated Item - because the PivotTable is showing both record counts and percentages (a value field set to either "% of column" or "% of row"), but Excel gives an error if the pivot table has calculated fields/items and percentages at the same time.
- Leaving everything visible, and putting the field codes directly into a PivotSelect statement; this also fails if there is no data to select. For example, Code:
2010-05-12, 07:37 #2
- Join Date
- Dec 2000
- Burwash, East Sussex, United Kingdom
- Thanked 198 Times in 184 Posts
Personally I would use a lookup table and an additional column in your source data to create a group field. It's a lot simpler.Regards,
Microsoft MVP - Excel