How to ungroup data in a PivotTable using VBA?

To ungroup data in a PivotTable using VBA, you can utilize a simple macro. When you group items in a PivotTable, Excel creates a new field for the grouped items. To ungroup them, you can use the `.ClearAllFilters` method if you want to remove all grouping, or the `.Ungroup` method if you are targeting a specific area.

Here’s a step-by-step guide and a sample VBA code to ungroup data in a PivotTable:

Sub UngroupPivotTableData()
    Dim ws As Worksheet
    Dim pt As PivotTable
    Dim pf As PivotField
    
    ' Set the worksheet and the PivotTable
    Set ws = ThisWorkbook.Sheets("Sheet1") ' Change "Sheet1" to your data sheet
    Set pt = ws.PivotTables("PivotTable1") ' Change "PivotTable1" to your PivotTable name
    
    ' Loop through each PivotField in the RowFields and ColumnFields
    For Each pf In pt.RowFields
        On Error Resume Next
        pf.ClearAllFilters  ' To clear all filters, including ungrouping
        pf.Ungroup          ' To ungroup specific grouped fields
        On Error GoTo 0     ' Reset error handling
    Next pf
    
    For Each pf In pt.ColumnFields
        On Error Resume Next
        pf.ClearAllFilters
        pf.Ungroup
        On Error GoTo 0
    Next pf
End Sub

Open the VBA Editor:

Press `ALT + F11` to open the Visual Basic for Applications editor.

Insert a Module:

In the VBA editor, right-click on any of the items for your workbook, select `Insert`, and then `Module` to insert a new module.

Enter the VBA Code:

Copy and paste the following VBA code into the module. This example assumes that you know the name of your PivotTable and field.

Modify the Code:

Change `”Sheet1″` to the name of the worksheet containing your PivotTable.
Change `”PivotTable1″` to the exact name of your PivotTable.

Run the Macro:

You can run the macro by pressing `F5` within the VBA editor or by assigning the macro to a button or shortcut in Excel.

This VBA script will loop through all the row and column fields in the specified PivotTable and attempt to ungroup them. Note that attempting to ungroup a field that isn’t grouped will not cause an error due to the error handling (`On Error Resume Next`), which allows the script to proceed without interruption.

facebooklinkedin