filter pivottable in VBA

arnoudlansdorp

New Member
Joined
Sep 14, 2006
Messages
0
Hello,

I have created a pivottable which contains quite a large amount of data. I have protected my workbook as well, so users can't change the sheets. This means they can't use the pivottable either. If they want to filter names in a row or column in the pivottable they need to use a listbox, with muliple selections.

This is part of the code:

Num = ListFilter_issuer.ListCount

For a = 1 To Num

Variable(a) = Worksheets("Input").Cells(a + 1, 12)

With Sheets("IR Delta").PivotTables("Credit Trading Exposure").PivotFields(TextStr(a))

.PivotItems(Variable(a)).Visible = ListFilter_issuer.Selected(a- 1)

end with

next

It works, but it is rather slow. There can be a lot of names in the row and for each name it decides if it has to be visible in the pivot or not. When using the normal pivottable filter it is much, much faster. How can I use VBA, but still maintain the speed of the pivot?

Regards,

Arnoud
 

Some videos you may like

Excel Facts

Test for Multiple Conditions in IF?
Use AND(test, test, test, test) or OR(test, test, test, ...) as the logical_test argument of IF.

Watch MrExcel Video

Forum statistics

Threads
1,111,910
Messages
5,541,542
Members
410,549
Latest member
Anaarchie
Top