Filter dynamic table (Power Pivot) by Listbox

Question

This code: /a/309087/116347

Make the selection in Listabox ... and Filter activated by a button.. as described in the link...

But if the Dynamic Table is created by Power Pivot .. This code does not apply...

It is possible to do the same work in this created table..

Thank you in advance.

Answer 1

I resolved it this way:

A formula named to find the last completed row of a column.. and then ... create a dynamic named range with the previously made "last" formula...as shown below.

I use a Listbox to filter by word between options. /a/309087/116347

I customized according to my need

Every time I clear the Range search field("$C$18").. Clears the "Selected" Range".

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

TextBox1 = Range("E18").Text

If Range("$E$18").Value = Empty Then
Range("$E$18").Value = "Faça a busca por endereço aqui"

Call ClearReportFiltering

Else
End If

If Range("$E$18").Value = "Faça a busca por endereço aqui" Then

Plan1.Columns(2).ClearContents

Else
End If
End Sub

Macro to Clean TD Filter

Sub ClearReportFiltering()
'PURPOSE: How to clear the Report Filter field

Dim pf As PivotField

Set pf = ActiveSheet.PivotTables("Tabela dinâmica9").PivotFields("[Base 1].[Endereço].[Endereço]")

'Option 1: Clear Out Any Previous Filtering
  pf.ClearAllFilters

  End Sub

Macro Para filtrar

Sub Filtrar()
Dim pf As PivotField
Dim myArray() As Variant
Dim myR As Range

Set myR = Range("Selecionados")
Set pf = Sheets("Geral").PivotTables("Tabela dinâmica9").PivotFields("[Base 1].[Endereço].[Endereço]")


ReDim myArray(0 To myR.Cells.Count - 1)

'Populate the array

For i = 0 To myR.Cells.Count - 1
myArray(i) = "[Base 1].[Endereço].&[" & myR.Cells(i + 1).Value & "]"
Next i

pf.VisibleItemsList = myArray

End Sub

By pressing a button with this code down ..

Macro to take the selected items from Listbox to column "B" and call the TD Power Pivot ribbon

Sub ClickColuna()


Sheets("Geral").Select

If Range("B2").Select = "" Then
Else

Range("B2").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.ClearContents
Range("B2").Select

End If

Dim i As Long
Dim J As Long
Dim arrItems()
    ReDim arrItems(0 To ListBox1.ColumnCount - 1)
    For J = 0 To ListBox1.ListCount - 1
        If ListBox1.Selected(J) Then

            For i = 0 To ListBox1.ColumnCount - 1
                arrItems(i) = ListBox1.Column(i, J)
            Next i

            With Sheets("Geral")
                .Cells(.Rows.Count, "B").End(xlUp).Offset(1).Resize(, ListBox1.ColumnCount).Value = arrItems


            End With
        End If
    Next J

Call Filtrar

End Sub