none
VBA vs regular pivot table? RRS feed

  • Question

  • I have a pivot table that is pulling data from a very large corporate data set. It has 9 total filters, and takes 10 minutes to run. Some of these filters include Current Year, Current Month, and Current Day, so if the data set is being filtered on those alone, I'm already at a small fraction of the whole raw data table, and I would expect results to be returned much more quickly.

    Question 1: Do pivot tables check every record against all the filters, or is there any way to ensure that the time filters are checked first? For example, does the order of the filters affect the underlying query in any way?

    Question 2: Are there any good explanations and samples showing how to better optimize a pivot table via VBA code? I've searched and found pages on how to create and refresh a pivot table with VBA, but I'm still missing any discussions on optimizing for speed.

    Thank you!

    Friday, December 5, 2014 12:09 AM

Answers

  • These are questions only the developers of Excel could answer with 100% accuracy.

    However, common sense could prevail to provide a decent answer.
    One huge issue: are your date fields INDEPENDENT of each other or are the part of a date GROUP arrangement ?
    Excel likely favors the GROUP arrangement in terms of performance....as it "knows" the relationship between date and month, etc.

    Filter performance is likely improved by applying the MOST RESTRICTIVE filters first.
    In that manner, the remaining filters have fewer records to deal with.

    If using VBA, expect performance to be horrendous if you are attempting to use Date as a filter criteria with individual days being selected or de-selected. Best to use ranges instead....or better yet, higher level of criteria like Month or Quarter.

    Finally, Always..>ALWAYS set the pivot table ManualUpdate property to true before applying a new filter.

    Friday, December 5, 2014 4:40 PM