Filtering Power Pivot Table by ListBox

Question

Filtering Power Pivot Table by ListBox

Navigation

#1 by (1 votes)

1

This code: link

Make the selection in the ListBox ... and Filter driven by a button .. as described in the link ...

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

You can do this same job in this created table.

Thank you in advance.

select vba pivot excel-vba powerpivot

asked by anonymous 07.08.2018 / 21:47

1 answer

Inner Join with like, Mysql, and Datatable Problem with JavaScript Old Game

score 1 · Accepted Answer

I have decided this way:

A formula named to find the last row filled in from a column .. and then ... create a named dynamic range with the "last" formula made earlier ... as I show below.

IuseaListboxtofilterbywordbetweenoptions. link

Customize as needed

Every time I clear the Search field Range ("$ 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 the 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 To filter

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 pushing a button with this code down.

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

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