VBA More Efficient 'Clear Slicers' Code

optimize vba code
vba speed up
how to speed up macros in excel 2016
vba optimization algorithm
excel vba efficient looping
eventstate vba
vba best practices
excel macro running slow

I've been working with six slicers on a summary sheet in Excel 2010. Each slicer is connected to 26 pivot tables.

I've used the code below in the past to clear slicer selections and it has worked just fine with minial lag. However, this time the code takes 6-7 seconds to fully execute, which seems hardly worth the functionality.

Dim oSlicerC As SlicerCache

For Each oSlicerC In ActiveWorkbook.SlicerCaches
    oSlicerC.ClearManualFilter
Next oSlicerC

I've been looking for a way to speed up the macro/make it more efficient, but I can't find anything online. I tried setting ScreenUpdating, DisplayAlerts, and EnableEvents to False and I tried setting Calculation to xlCalculationManual at the beginning of my code, but that didn't help much.

I thought about iterating through each SlicerItem to de-select each one, but with up to 100 items in a few slicers I'm not sure that would be any faster.

Does anyone have any thoughts on how I might be able to accomplish this? I'm not looking for code as I'd like to take a stab at it myself, but I'm not sure which path I should take or if there's even a more efficient alternative to what I'm already using. Many thanks!

I had a similar problem, settled on:

Dim oSlicerC As SlicerCache
For Each oSlicerC In ActiveWorkbook.SlicerCaches
If oSlicerC.FilterCleared = False Then oSlicerC.ClearManualFilter
Next oSlicerC`

Excel VBA Speed And Efficiency, It is also more efficient to perform all numerical calculations in VBA. It is frequently tempting to leave formulas in the spreadsheet and call them from the macro. Maximizing Your Efficiency With VBA. Whether you are sending emails through Outlook, documenting your work in Word, putting together some tables and graphs in Excel, or creating presentations using PowerPoint, there is one common skill that you can learn to make yourself more efficient while doing almost anything in these applications: learn Visual Basic for Applications (VBA).

It's an old post but there is also another possibility.

If you have the data in an excel table you can call on that table

tbl.AutoFilter.ShowAllData

This will also reset all slicers very fast.

with 173'561 rows and other things in between my program.

for each example above: 6.88 sec my example: 6.92 sec

Perhaps there are situation where one or the other is faster. e.g. it has to reset more than just a few slicers my version should be faster.

Optimize Slow VBA Code. Speed Up Efficient VBA Code/Macros, Optimize Excel VBA code with these tips and tricks for Speeding Up Excel More often than not people would use an If, Else Statement to test whether a  Optimize Excel VBA code with these tips and tricks for Speeding Up Excel Macros Optimize Slow VBA Code. Speeding Up Slow Excel VBA Code More often than not

i see it was posted long ago, but still this could be usefull: i just defined 2 parameters for sliceritems, and while looping on first items, ran another loop to unselect all. '''

For Each oSi In oScR.SlicerItems
    If oSi.HasData Then
        For Each oSiT In oScR.SlicerItems:
            If oSiT.HasData Then oSiT.Selected = False
        Next
        oSi.Selected = True

    End If
Next

'''

Excel VBA Performance Coding Best Practices, In this post I'm going to share with you the most important performance tips I know about. There are tons of sites, pages, and people who are  There are many different articles and post on improving Excel performance. What I personally was missing was a simple, comprehensive, short overview of how the performance of VBA macros can be improved without needing to read through long articles on how the Excel compiler works.

Optimize VBA Code to run Macros Faster, Optimize VBA code to run macros faster, simple, easy to understand and efficient way of writing macro. Please find the more details and  For more information, see Developing Excel 2010 XLLs. The performance of VBA user-defined functions is sensitive to how you program and call them. Use faster VBA user-defined functions. It is usually faster to use the Excel formula calculations and worksheet functions than to use VBA user-defined functions.

Maximizing Your Efficiency With VBA, VBA is the best language for most beginners because it offers one of the most efficient ways to learn a programming language in terms of time  Excel VBA Performance Coding Best Practices (even more so if your code uses loops). Just one recalculation and one redraw at the end of your code execution is

Two ways to speed up Excel macros, The Table references the Species column in a Table named Table3. To learn more about efficient selection methods when using VBA, read Excel  Correct/Efficient Uses of Excel Loops. See Also: Excel VBA Loops Explained. Back to: Excel VBA. Got any Excel/VBA Questions? Free Excel Help. Don't get caught in the Loop: To put it bluntly I very often avoid Loops, they are far too slow in many cases. A common mistake we all make when first learning VBA is to use Loops when we really shouldn't.

Comments
  • Thanks for the answer. I've never seen .FilterCleared before. Without testing, I don't think this resolves my issue because the long runtime is due to the number of items and pivots each slicer is connected to.