VBA Autohide/Unhide Execution Speed

Try this.
- Turn off screen redrawing during the macro's execution. See the bold instructions in the code module below.
This will be the main time saver I think.

Other tips:
- The IF decision can be simplified (no ELSEIF decision is needed) although that's not as significant.
- The value of variable LastRow is not being used in the macro so it's calculation line could be removed.
Or perhaps you want For Each c in Range("A6:A" & LastRow) to start your loop. You'll have to decide.
- The entire IF/ELSE/END structure could be written as c.EntireRow.Hidden = (c.value<>"X").
I myself prefer your method though.
- A more complex method is to build an array of row numbers to be hidden, and then hide them all at once after the loop. (Not shown here)
- One other suggestion is to make all the rows visible before the loop (done quickly), and then hide only the ones without an "X".
This is what is shown below.

Please Login or Register  to view this content.

The Worksheet_Calculate event procedure is probably not the best way to do this.

Originally Posted by xtremca

The issue that I'm running across is how to speed up the VBA as it executes each time the user selects a response.

It's probably best to use the Worksheet_Change event procedure and trigger off of whatever cell the user is changing. This example uses cell A1 as the user-triggered change and auto filters from A6 to the last used cell in column A. (Remember to comment out the Worksheet_Calculate procedure).

Please Login or Register  to view this content.

Originally Posted by xtremca

If I recall, I had some issue with worksheet_change not updating the auto hide/unhide on my A column fields that had a formula driven response. This is what led me to the worksheet_calculate. My A column "X" can be dependent on a previous A column X or dependent on and If(and of a previous A column response and item code (column E on the attached). I've attached a sample worksheet similar to my project to show the A column formulas, etc. On the attached, Column A and E are normally hidden from the user as it's just used for my lookups and auto sorting.

Again, use the User-changed cells in column C to qualify the Worksheet_Change trigger and not the column A formulas. Once the trigger is qualified as column C user-changed, then use the column A formula results to determine which rows to hide\unhide.

Something like...

Please Login or Register  to view this content.

Reminder: don't use both this and the Worksheet_Calculate procedure.

Originally Posted by AlphaFrog

Again, use the User-changed cells in column C to qualify the Worksheet_Change trigger and not the column A formulas. Once the trigger is qualified as column C user-changed, then use the column A formula results to determine which rows to hide\unhide.

Something like...

Please Login or Register  to view this content.

Reminder: don't use both this and the Worksheet_Calculate procedure.

Not sure what I was doing before that I had such an issue with the WKsheet_change, but it's definitely gone turbo speed. Now my only issue is that I was already using a wksheet_change to make certain cells default back to a position so if the user made a change to a prior selection, it would make a corresponding selection menu default from a choice to "please select".

Here is the code including what you have provided me along with my auto resets. How do I go about combining these so they can both be used since as if I'm thinking correctly, you cannot stack more than one worksheet_change at one time. (at least separated)

Please Login or Register  to view this content.

You're welcome.

Not having the benefit of seeing your actual worksheet, I'm guessing it may be something like this...

Please Login or Register  to view this content.