Loop Alternatives/performance increases

[Kramxel]

Hi,

I use the code below on a couple of combo boxes to help filter some data, it's relatively quick but there is a noticeable pause. is there a method of reducing this pause, i'm looking into variant arrays but its taking time to sink it and "click" but wondering if there's anything else?

Please Login or Register  to view this content.

[cytop]

You could try this... (you need to add back the Application settings at the start and end):

Please Login or Register  to view this content.

I don't understand the Champion & Challenger block.

Please Login or Register  to view this content.

What's the purpose of the 2 additional IF... statements in the CASE block?

[JBeaucaire]

Possibly use Excel's Autofilter capability to hide/unhide rows instead of looping through rows doing it manually. That should be instantaneous.

[Kramxel]

Hi,

thank you for your repsonse, i've tried the code and it doesn't seem to allow the rows to be unhidden if all is selected? the purpose of the champion and challenger block is to allow two "catergories" to be visible while the others are hidden.

[Kramxel]

Possibly use Excel's Autofilter capability to hide/unhide rows instead of looping through rows doing it manually. That should be instantaneous.

I'm trying to replicate that functionaility actually, and due to how this sheet has been constructed and other sheets dependencies on this sheet its not ideal to use autofilters.

[JBeaucaire]

The AutoFilter allows multiple items to be shown while all others hidden. Correcting design errors to allow the Autofilter to function correctly would be the ideal path.

[Kramxel]

The AutoFilter allows multiple items to be shown while all others hidden. Correcting design errors to allow the Autofilter to function correctly would be the ideal path.

While I agree with you entirely, its just not feasible here. Re-constructing this sheet would then require many many hours re-constructing other sheets and formula's - so a work around/alternative it must be.

[JBeaucaire]

Then your existing code is pretty much how to do it. You're individually setting the hidden property of 90 rows each and every time you change the combo box. That means 90 updates to the sheet, that's the pause, and that will take as long as it takes. You've turned off everything you can to speed it up, from what I can see.

[Kramxel]

Thank you.

[Richard Buttrey]

While I agree with you entirely, its just not feasible here. Re-constructing this sheet would then require many many hours re-constructing other sheets and formula's - so a work around/alternative it must be.

Hi,

Would you like to share your workbook with is so that we can check whether the autofilter is unfeasible.
JB is right, it's a particular hobby horse of mine. I've lost track of the number of times I've advised people that loops should only ever be the last resort for this sort of stuff for the very reasons you are encountering. An Autofilter is the fastest way of doing this. This really should be a FAQ!

If your existing columns don't easily lend themselves to autofiltering exactly what you want easily then a technique I often use is to add a helper column which works out from the other columns with various IF AND OR tests whether the particular row should be filtered. Then this single helper column can be used as the autofilter. There's no need to reconstruct the worksheet at all.

[apo]

Hi..

If you can't use autofilter.. and you're stuck with a loop.. maybe at least try building a range using union and then hiding the range in one hit (at the end once the range is built).. rather than doing it every time a cell meets the condition.

[Kramxel]

Hi..

If you can't use autofilter.. and you're stuck with a loop.. maybe at least try building a range using union and then hiding the range in one hit (at the end once the range is built).. rather than doing it every time a cell meets the condition.

I *think* that's what I've been reading about i'm just not understanding it quite yet - i'm hoping for that eureka moment when everything clicks and I realise A) how easy it actually is and B) how much of an ID10T I actually am.

[Kramxel]

Duplicate please ignore

[Richard Buttrey]

If you're stuck on using a loop then a better way of processing it would be to read the range into a VBA array and then process the array with a loop and use the loop counter to refer to the array and then build a second array of those row numbers that should be hidden.

The reason 'normal' loops take a long time is that they have to keep jumping backwards and forwards between the VBE and the Excel sheet to read and write. Each time the loop does this there is a time overhead. By reading all your data into a VBA array you process the arrays entirely in the VBE without all the jumping backwards and forwards, and only when you've finished the processing do you write the results or actions back to Excel.

But as I said in #10 why not upload the workbook. I'll be most surprised if you can't use an autofilter to do what you want. And it'll be almost instantaneous and only need two or three lines of code as opposed to the many Selcect & Loop lines you currently have.

[Kramxel]

Hi..

If you can't use autofilter.. and you're stuck with a loop.. maybe at least try building a range using union and then hiding the range in one hit (at the end once the range is built).. rather than doing it every time a cell meets the condition.

would you mind giving me a run down of how to do that given my example above: I like this as A) solves my immediate problem B) gives me something else to learn, but after reading up on it i'm still a little confused.

[romperstomper]

Untested, but something like this:

Please Login or Register  to view this content.

[apo]

Nice combo Rory..

@ Kramxel... Rory has combined Richards suggestion of doing it mostly within memory (read into array) and building a Range using the union method..

You should step thru (F8) his code , monitoring the Locals Window in the VB Editor to see what is going on..

[Kramxel]

i [have] been refreshing thread all afternoon and only just noticed it has spilled over to a second page

Thank you Rory and Apo just what I needed things are slowly begining to sink in - can see this being quite useful indeed, still going to take a while before it really takes hold but i'm sure i'll get there soon1

[Kramxel]

having played around with this works almost flawlessly

"The almost" is that no matter what the value in C26, if B26 is set to All, every row is unhidden when it should be all rows that have an entry in them other than what's displayed in C26 (hope that makes sense)

[romperstomper]

I'm done for the day, so I'll leave that as a learning exercise for you. Or someone else can fix it of course.

If you're still stuck tomorrow, let us know.