Trying to Make Macros Run More Efficiently

[Jim Clayton]

Hello everyone. Attached is a sample workbook full of macros. All of the macros work fine, but when the "Reset" button is clicked, an hourglass pops up and it's taking a while to reset. Previously you there was no hourglass, but you could actually see the sheet "thinking" so to speak. Attaching the new updated hourglass sheet, and the old thinking sheet so that you can see the difference. I'm basically just trying to get it to where when the "Reset" button is pressed, everything resets nice and smooth. There is a macro to hide/unhide slicers, and another re-align the cell formatting so I'm not sure were the problem lies. Would greatly appreciate any help. Tks.

[Jim Clayton]

Anyone think they might be able to take a crack at this? Would greatly appreciate the help.

[p45cal]

try:

Sub ClearMySlicers()
Dim Slcr As SlicerCache, itm
Application.ScreenUpdating = False
Sheets("BuildMyBOM").PivotTables(1).ManualUpdate = True
Application.EnableEvents = False
For Each Slcr In ActiveWorkbook.SlicerCaches
  Slcr.ClearManualFilter
  For Each itm In Slcr.Slicers
    itm.Shape.Visible = True
  Next itm
Next
Sheets("BuildMyBOM").PivotTables(1).ManualUpdate = False
Application.EnableEvents = True
Application.ScreenUpdating = True
End Sub

[p45cal]

or…

Sub ClearMySlicers()
Dim Slcr, itm, pt, pfnm
Application.ScreenUpdating = False
Set pt = Sheets("BuildMyBOM").PivotTables(1)
pt.ManualUpdate = True
Application.EnableEvents = False
For Each Slcr In pt.Slicers
  Slcr.SlicerCache.ClearManualFilter
  Slcr.Shape.Visible = True
Next
pt.ManualUpdate = False
For Each pfnm In Array("QTY", "UOM", "Material", "Item Number")
  pt.PivotFields(pfnm).DataRange.HorizontalAlignment = xlCenter
Next pfnm
Application.EnableEvents = True
Application.ScreenUpdating = True
End Sub

[Jim Clayton]

Thanks or the help. This is a major improvement. The first one speeds things up but changes formatting (suspect you noticed that). The second one does make it substantially faster. I have one other piece of code (posted below) that I would like to try but 1) I don't know that it actually works and 2) I don't know how to go about incorporating it. I could use some help if anyone would be willing to assist. Again, Thank You. This makes things substantially faster.

tbl.AutoFilter.ShowAllData

[Fluff]

Cross posted https://www.excelforum.com/excel-pro...ml#post4947369

[Jim Clayton]

This is correct. The question was first asked here during my reply, then again on Excel Help Forum. A transcript can be found at the below link.
https://www.excelforum.com/excel-pro...-smoother.html

[p45cal]

I have one other piece of code (posted below) that I would like to try but 1) I don't know that it actually works and 2) I don't know how to go about incorporating it.

tbl.AutoFilter.ShowAllData

I doubt you'll be able to use it on a pivot table (it's typically for use on a Table (ListObject) or an autofilter on a sheet. The equivalent (clicking the same icon on the ribbon) is:
PivotTable.ClearAllFilters which you could use:
Sheets("BuildMyBOM").PivotTables(1).ClearAllFilters

Something else; your files seem to be rather big considering how little data they hold. I reconstructed your file in a new workbook (attached). Things are considerably lighter and faster, regardless of what method is used to reset the pivot.

[georgiboy]

I have come across the file size increase before, I found it happened most when formatting entire columns. Excel seems to think the data goes to the last row of the sheet.

[Jim Clayton]

The speed on this is perfect. Several things. When I try to take the sheet you did and make changes/save it, gussie it up so to speak, I run into a message box that states "Privacy warning: This Document Contains macros, ActiveX Controls, XML expansion pack information, etc, etc, etc." Any idea why that might be? It's also giving me some issues about formatting. So I transferred the code...the whole thing wasn't working quite like yours, but Clear Slicers 3 works at almost the same speed...just a not as smooth. Now you have me noticing file size. And this question is directed to anyone. Are there steps I can take to decrease file size? I'm sure google has answers, but I'm here so I'm asking. Again, this is great. Thanks for all of your help.

[p45cal]

I run into a message box that states "Privacy warning: This Document Contains macros, ActiveX Controls, XML expansion pack information, etc, etc, etc." Any idea why that might be?

File|Options|Trust Center, Trust Center Settings…, untick Remove personal information from file properties on Save.

[Jim Clayton]

You're currently operating at a 100% efficiency rate...I'm moderately tempted to just ask questions to try and stump you. Thanks for all of your help today. It's very much appreciated.