Results 1 to 2 of 2
  1. #1
    New Lounger
    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)

    Group ("rson2") --- "rson" codes to put into group
    Voluntary Redundancy --- TVR, VGR, VRS, VSS
    Forced Separation --- ABN, RCT, TOE
    Agreed Period Expired --- END, SEV
    (There are more, but that's enough for illustration)

    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
        End With
        PT.PivotSelect BaseField, xlDataAndLabel
        Set rng = Selection
        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
        With PT.PivotFields(GroupField)
            .Orientation = xlPageField
        End With
    End Sub
    Does anyone have any better ways of doing it? I'm trying to avoid having my macro rely on someone manually creating the groupings (using just whichever fields happen to be present, and/or dummying up data) before the macro is run.

    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,
      SourcePivot.PivotSelect "rson[TVR,VGR,VRS,VSS]"
      works if one or more of the rson codes TVR, VGR, VRS or VSS has data, but generates a run-time error if none of them do.



  2. #2
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Burwash, East Sussex, United Kingdom
    Thanked 187 Times in 173 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.

    Microsoft MVP - Excel

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts