CODEWITHSUNDEEP
vbaexcelpivot-table
Przemyslaw Remin
Przemyslaw Remin

Reputation: 6940

Filter ShowDetails of PivotTable

I am looking for efficient way to filter ShowDetails of PivotTable ActiveCell. I would like to narrow the results to specific columns and rows only.

Say, the original data (on which the pivot table is built) has ten columns. I want to show details for only two columns, and I want to select only the rows with a specific data i.e. no empty cells in particular column.

Here is my start point:

Sub ShowDetailsAndFilterResults()
    On Error Resume Next
    Dim PT As PivotTable
    Set PT = ActiveCell.PivotTable

    If Not PT Is Nothing Then
        Selection.ShowDetail = True            
    End If     
End Sub

For SQL users it is easy to imagine my desired solution of ShowDetails command to get only the results limited by SELECT with listing specific columns and with WHERE clause to get further conditions on rows.

Upvotes: 0

Views: 686

Answers (1)

R3uK
R3uK

Reputation: 14537

Here are some pointers that should help you to go through :

Sub ShowDetailsAndFilterResults()

    Dim PT As PivotTable, _
        WsPt As Worksheet, _
        WsD As Worksheet

    On Error Resume Next
    Set PT = ActiveCell.PivotTable
    Set WsPt = ActiveSheet

    If Not PT Is Nothing Then
        ActiveCell.ShowDetail = True
        Set WsD = ActiveSheet
        With WsD
            '---Delete useless columns---
            .Range("C:C,E:E,G:G,H:H").Delete Shift:=xlToLeft
            '---Or just Hide useless columns---
            'Range("C:C,E:E,G:G,H:H").EntireColumn.Hidden = True

            '---Scan rows to filter them---
            For i = .Range("A" & .Rows.Count).End(xlUp).Row To 2 Step -1
                If .Cells(i, 4) <> "test_value" Then
                    '---place the code regarding the test you have to do---
                Else
                    '---lets say you want to keep the rows that are different of "test_value" on column 4
                    .Range("A" & i).EntireRow.Hidden = True
                    '---Or delete the rows that doesn't fit your criteria---
                    '.Range("A" & i).EntireRow.Delete Shift:=xlUp
                End If
            Next i
        End With
    End If

    On Error GoTo 0
End Sub

If you have strictly less than 3 criterias for the filtering of the results' rows, you could use the AutoFilter method, but I found it not that flexible so generally I prefer to use loops like I showed up here.

Hope this help you

Upvotes: 1

Related Questions