Multi-select Listbox to filter pivot table runs slow

[boazstuff]

Hi all,

I have a multi select listbox which I used to filter pivot table pagefields. (method 1)<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
I also have a code to filter the pivot table and update the selection on the list box. (method 2)<o></o>
The code works perfectly fine except that it is very slow using method 2.<o></o>
<o></o>
I understand it is due to a lot of loopings but I am not sure how I can improve it…<o></o>
<o></o>
Appreciate if someone can enlighten and help, thanks.

Regards,
Boaz<o></o>
<o></o>
Private Sub ListBox1_Change()<o></o>
<o></o>
Dim rngSelection As Range<o></o>
Dim lngItem As Long<o></o>
<o></o>
Set rngSelection = Worksheets("Control").Range("MultiListOutPut")<o></o>
rngSelection.Value = 0<o></o>
<o></o>
Application.ScreenUpdating = False<o></o>
<o></o>
For lngItem = 1 To ListBox1.ListCount<o></o>
<o></o>
If ListBox1.Selected(lngItem - 1) Then<o></o>
If rngSelection(lngItem).Value = 1 Then<o></o>
rngSelection(lngItem).Value = 0<o></o>
Else<o></o>
rngSelection(lngItem).Value = 1<o></o>
End If<o></o>
End If<o></o>
<o></o>
Next lngItem<o></o>
<o></o>
Application.ScreenUpdating = True<o></o>
<o></o>
Call Pivot<o></o>
<o></o>
End Sub

<o></o>
<o></o>
Sub Pivot()<o></o>
<o></o>
Dim PTGWP As PivotTable<o></o>
Set PTGWP = Sheets("Pivot").PivotTables("PTGWP")<o></o>
<o></o>
Application.Calculation = xlCalculationManual<o></o>
Application.ScreenUpdating = False<o></o>
<o></o>
PTGWP.PivotFields("Product Line").CurrentPage = "(All)"<o></o>
With PTGWP.PivotFields("Product Line")<o></o>
.PivotItems("BD").Visible = Range("BD")<o></o>
.PivotItems("CL").Visible = Range("L")<o></o>
.PivotItems("CM").Visible = Range("CM")<o></o>
.PivotItems("CP").Visible = Range("CP")<o></o>
.PivotItems("CO").Visible = Range("CO")<o></o>
.PivotItems("CE").Visible = Range("CE")<o></o>
.PivotItems("EC").Visible = Range("EC")<o></o>
.PivotItems("EN").Visible = Range("EN")<o></o>
.PivotItems("GH").Visible = Range("GH")<o></o>
.PivotItems("IH").Visible = Range("IH")<o></o>
.PivotItems("IP").Visible = Range("IP")<o></o>
.PivotItems("MC").Visible = Range("MC")<o></o>
.PivotItems("MH").Visible = Range("MH")<o></o>
.PivotItems("PA").Visible = Range("PA")<o></o>
.PivotItems("PL").Visible = Range("PL")<o></o>
.PivotItems("PM").Visible = Range("PM")<o></o>
.PivotItems("PP").Visible = Range("PP")<o></o>
.PivotItems("PR").Visible = Range("PR")<o></o>
.PivotItems("SL").Visible = Range("SL")<o></o>
.PivotItems("SP").Visible = Range("SP")<o></o>
.PivotItems("TR").Visible = Range("TR")<o></o>
End With<o></o>

<o></o>
Application.Calculation = xlCalculationAutomatic<o></o>
Application.ScreenUpdating = True<o></o>
<o></o>
Set PTGWP = Nothing<o></o>
<o></o>
End Sub<o></o>

 

[boazstuff]

Very sorry, i forgot to paste another code which is the code that runs slow. When this code is fired, it will run through a long loops with the Listbox1_change macro. And that is the reason for the slowness...


Sub ListBox1_lines()
Dim rngSelection As Range
Set rngSelection = Worksheets("Control").Range("MultiListOutPut")
rngSelection.Value = 0
Application.Calculation = xlCalculationManual
Application.ScreenUpdating = False
ListBox1.Selected(0) = False
ListBox1.Selected(1) = False
ListBox1.Selected(2) = True
ListBox1.Selected(3) = False
ListBox1.Selected(4) = False
ListBox1.Selected(5) = False
ListBox1.Selected(6) = False
ListBox1.Selected(7) = False
ListBox1.Selected(8) = True
ListBox1.Selected(9) = True
ListBox1.Selected(10) = False
ListBox1.Selected(11) = False
ListBox1.Selected(12) = False
ListBox1.Selected(13) = True
ListBox1.Selected(14) = True
ListBox1.Selected(15) = True
ListBox1.Selected(16) = True
ListBox1.Selected(17) = False
ListBox1.Selected(18) = False
ListBox1.Selected(19) = True
Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic
End Sub