+ Reply to Thread
Results 1 to 6 of 6

Code to loop through pvt slicer very slow

  1. #1
    Forum Contributor
    Join Date
    07-23-2012
    Location
    London, England
    MS-Off Ver
    Excel 2007
    Posts
    211

    Code to loop through pvt slicer very slow

    Hi

    I have a pivot table and a linked slicer which has dates as items. To cover a calendar year, the number of items is 365.

    I also have a userform where FromDate and ToDate variables can be selected (via MSCalendar). On choosing the two dates, the following code enables the selected date items on the slicer.

    However, the code seems to take ages to go through each slicer element via code.

    Can someone figure out why it would be so slow?

    Thanks

    Please Login or Register  to view this content.

  2. #2
    Forum Moderator - RIP Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    29,464

    Re: Code to loop through pvt slicer very slow

    Hi,

    Any chance you could upload the workbook so that we can see the problem in context?
    Richard Buttrey

    RIP - d. 06/10/2022

    If any of the responses have helped then please consider rating them by clicking the small star icon below the post.

  3. #3
    Forum Contributor
    Join Date
    07-23-2012
    Location
    London, England
    MS-Off Ver
    Excel 2007
    Posts
    211

    Re: Code to loop through pvt slicer very slow

    Sorry about the delay, Richard and thanks for your reply.

    I had to significantly reduce the base data to save this within the 1mb limit

    I attach the 'Module'.

    Cheers
    Attached Files Attached Files

  4. #4
    Forum Contributor
    Join Date
    07-23-2012
    Location
    London, England
    MS-Off Ver
    Excel 2007
    Posts
    211

    Re: Code to loop through pvt slicer very slow

    Any thoughts?

  5. #5
    Forum Moderator - RIP Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    29,464

    Re: Code to loop through pvt slicer very slow

    Hi,

    I did some tests on the AllSLicer procedure which seems to be the first one to run and which as you say is slow.
    The timings for 50 iterations through the slicer select loop takes about 6 seconds on my laptop, and 100 iterations 12 seconds. However when I increased the iterations to 150, it started to slow down after about 110 iterations and took 24 seconds, with 200 iterations taking 41 seconds.

    These timings were all starting with the slicers deselected. What is interesting is that if after a first iteration you leave the items selected and run the iteration again it slows down even more. 100 iterations taking 37 seconds.

    It seems that there is something inherent in the underlying machine code that handles slicers that are already selected that is just slow. Perhaps we shouldn't be surprised and it seems to be an extreme case of the general rule when programming VBA that Selecting stuff should be avoided.

    What's the ultimate goal here. I seem to remember that preslicer days various people had created procedures to identify which values in an autofilter list had been picked and maybe that e would offer you a more practical way forward.

    If I think of anything else I'll post back. I suspect there has not yet been much general analysis of slicers and we're still learning a lot about them. The fact that they seem to be rarely used (IMO) doesn't help either.

  6. #6
    Forum Contributor
    Join Date
    07-23-2012
    Location
    London, England
    MS-Off Ver
    Excel 2007
    Posts
    211

    Re: Code to loop through pvt slicer very slow

    Thanks for coming back, Richard. Hopefully you will think of a work around to this problem.

    Cheers

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Macro on slicer is slow
    By surveyman1 in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 12-11-2013, 01:20 PM
  2. [SOLVED] Slow VBA Code for simple Loop and add pasted values Any Way to Speed Up
    By John Vieren in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 10-10-2013, 11:16 AM
  3. [SOLVED] Change Chart Title Depending on a Slicer - slicer advice
    By JungleJme in forum Excel General
    Replies: 8
    Last Post: 08-17-2012, 07:59 AM
  4. [SOLVED] Slow VBA Loop Code
    By krcaldwell31 in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 04-30-2012, 09:14 AM
  5. My code is running pretty slow with the loop that i have
    By Josh_123456 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 04-20-2007, 07:20 PM

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