+ Reply to Thread
Results 1 to 5 of 5

Filter Pivot Table using VBA

  1. #1
    Registered User
    Join Date
    07-24-2009
    Location
    Ireland
    MS-Off Ver
    Excel 2003
    Posts
    13

    Filter Pivot Table using VBA

    Hi,

    I am trying to create a macro which will filter a large pivot table for a set of specified accounts. The pivot table contains accounts for multiple sales staff (total number accounts approx 50k). For sales people to see only certain accounts they need to manually select them in the pivot filter but this is a slow process for them (also error prone as they may miss an account or select the wrong one). The idea is that they can paste a list of the account id's into another sheet and run a macro which takes these accounts and filters the pivot table only to show them.

    The good thing is that this works but it is really really slow. When I stepped through the code I see that the loop I set up is going through the pivot table account by account and either selecting it or de-selecting it and then it refreshes the pivot table each time. This takes forever because it has to go through the full 50k accounts.
    I assumed there must be a way to stop the pivot table from refreshing until the loop has finished going through all the accounts. I came across this bit of code:
    Please Login or Register  to view this content.
    which seems to turn off the automatic pivot table updating but it doesn’t seem to have any noticeable impact on the speed of the macro.
    I decided to record a manual selection of accounts and I was surprised to see how Excel handles it:
    Please Login or Register  to view this content.
    This continues for every account in the table and would explain why the macro is so slow to run. The loop is effectively doing the same thing.
    I know that if I want to do a similar thing with an auto filter and I record it then it will look something like this:
    Please Login or Register  to view this content.
    So here it uses an array to select what I want to see rather than going through every single account and setting it to visible or invisible.
    Is there any way to use this approach with a pivot table or are the pivot filters handled in a different way?

    Sorry if this is a long and rambling post but I am trying to give as much information as I can. Is this just a VBA limitation or is there a way around this?

    Thanks!

  2. #2
    Registered User
    Join Date
    03-08-2007
    Posts
    93

    Re: Filter Pivot Table using VBA

    I think there's a simpler way to accomplish this. Add a column to your pivot table called "Inlist". In this column, put a vlookup formula to determine whether or not this line item is in your target list (1 = yes, 2 = no). The set your pivot criteria for Inlist = 1.

    SAE

  3. #3
    Registered User
    Join Date
    07-24-2009
    Location
    Ireland
    MS-Off Ver
    Excel 2003
    Posts
    13

    Re: Filter Pivot Table using VBA

    Thanks, that idea would work except for one thing - the data is from an external connection and as a result I don't know if it is possible to add a column into the pivot table. Is there any way of doing this? If I simply just try to insert a column I get an error message telling me that I cannot simply insert a column.

  4. #4
    Registered User
    Join Date
    07-24-2009
    Location
    Ireland
    MS-Off Ver
    Excel 2003
    Posts
    13

    Re: Filter Pivot Table using VBA

    Ok so I can't modify the external data and I can't add a column into the pivot table from within Excel. So that leaves me with the macro which runs - slowly. Approx 1 1/2 hours to run it and I have included code to turn off all updating, calculations, etc.

    So am I just to accept that Excel/VBA is slow at this kind of thing and request a faster machine to run this macro on!

  5. #5
    Registered User
    Join Date
    05-01-2009
    Location
    Amsterdam, Netherlands
    MS-Off Ver
    Excel 2007
    Posts
    26

    Re: Filter Pivot Table using VBA

    Hi there,

    I am facing the same problem. It's fairly easy to filter a pivot table for one entry:

    Please Login or Register  to view this content.
    so, my table will show only the rows that have "red" in the column named "filters".

    Another table will have to show all BUT the rows that have "red" in the column named "filters".

    Making it harder: I don't know in advance which values will be showing up in my "filters" column...

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1