For Each loop taking long to run

[Jay S.]

Hello experts.

I have some code that runs through a few thousand lines of data to look for two conditions and if found copy to a separate spreadsheet to load into a listbox. I'm sure there are many better ways to achieve this which is why I'm reaching out for assistance. I've tried all the usual things to speed it up, but am now at a loss. Currently it takes about 30-40 seconds to complete to even show the userform.

Specifics:
* 2 worksheets (CMR's 2018 and CMR Tracking)
* 26 columns used across
* a few thousand rows (sheet will be used for entire year)

What it's looking for:
If column Y has a date and Z does not (Return the row)
If column W has a name and Y has no date (Return the row)

Here's the code - please suggest away!

Please Login or Register  to view this content.

[davesexcel]

Have you tried just using the autofilter? It would filter your conditions, then you can copy and paste the entire filtered data.

[Vraag en antwoord]

Can you place an example of the file?
I do not understand the conditions.

Please Login or Register  to view this content.

[Jay S.]

I have not. Great suggestion!

Do you mean to autofilter manually and then copy with VBA, or is there a VBA solution to perform both?

[Vraag en antwoord]

Just a guess without a sample file.

Please Login or Register  to view this content.

[Jay S.]

Sample attached.

[Jay S.]

What it's looking for:
If column Y has a date and Z does not (Return the row)
If column W has a name and Y has no date (Return the row)

[davesexcel]

I have not. Great suggestion!

Do you mean to autofilter manually and then copy with VBA, or is there a VBA solution to perform both?

You can practice doing it manually, then once you have that perfected, use the macro recorder to get a code, you can then ask for assistance to get the code cleaned up.

[Jay S.]

lol...oh ya! Thanks Dave

[jindon]

AdvancedFilter...

Please Login or Register  to view this content.

[Zer0Cool]

As you have noticed For each loops on a range is slow.

As other have mentioned your best approach for speed is to either use the autofilter or advanced filter. Autofilter is easier to setup and control, but you have to then copy the results to the desired destination. Advanced filter requires some setup (as the criteria needs to be on sheet if I recall) but it can dump results to a destination which is more efficient than copying the data yourself and its able to handle more complex conditions.

You also mention loading the results to a listbox on another sheet. Do you mean a drop down validation list, a listbox control, a activex listbox or a listbox in a form? In any case you may consider loading the results into an array, collection or dictionary instead of on sheet into a range. First, it wouldnt increase file size as the results wouldnt be stored on sheet and it would likely be easier to work with.

[Jay S.]

Hi ZerOCool,

Thanks for the reply and information. I'm attempting to use jindon's AdvancedFilter above your post, but am only getting the first column of data returned in the CMR Tracking sheet. Rather than ask how to fix it (the easy way), I'm working with the code to try and learn.

I try to do as much as I can without asking for help to try and learn before just getting the answer. I may throw out the "Help" post in a bit but for now, I'll keep at it.

Thank you for your time and suggestions. I've not used "collection" or "dictionary" before - something new to research!

Jay

[Vraag en antwoord]

try this

Please Login or Register  to view this content.

[Norie]

Jay

Give this a try.

Please Login or Register  to view this content.

PS You could actually populate the listbox directly from the array but I'm not sure if you need the CMR Tracking sheet for other purposes so I've dumped the array there rather than do that.

[Zer0Cool]

I've not used "collection" or "dictionary" before - something new to research!Jay

You have before and didnt even know it

In your For Each loop, you were looping each range object (cell) of the range collection you specified. There are many built in collections. Sheets/Worksheets, Workbooks, Range, Tables, Charts, etc. Most people work with these (and the concept of them) often without ever considering it.

The concept is no different when dealing with custom collections. You are simply storing like information in the same paradigm as the built in collections, which then let you access them and manipulate them in the same fashions. A benefit of a collection vs an array is that resizing is much easier in a collection. Adding/removing elements is pretty simple. It also allows using the For each construct to loop instead of using a counter and Ubound. The downside is in large data sets collections can be slower than arrays and dictionaries and the values of the items in the collection are read-only.

Heres a good primer on collections https://excelmacromastery.com/excel-vba-collections/

A dictionary is kind of like a combination of an array and collection. A dictionary is faster than a collection (and in some cases faster than an array) and it provides the benefits of a collection, plus you can check if items exist in the dictionary already as well as change the values of existing elements. The downside is you either need to early bind and reference the Microsoft Scripting Runtime or late bind (which means VBE may not give you hints about what properties, methods and arguments to use as you type.) A dictionary is also a bit harder in concept to understand.

Heres a good intor to dictionaries https://excelmacromastery.com/vba-dictionary/

Each has its place, as you gain more experience programming you will start to see when to use each in your code.

[Jay S.]

Thanks very much ZerOCool.

I've never heard those two terms (Collection and Dictionary). I appreciate the education and links.

Jay

[Jay S.]

All,

Here's what I ended up going with:

Please Login or Register  to view this content.

Once I understood how the code works, it made a lot of sense. I appreciate all your suggestion, education and efforts!

Jay

[Norie]

Jay

Just curious, did you try the code I posted?

[Vraag en antwoord]

@Norie,
Your code contains a lot of unnecessary declarations and is also extremely slow compared to the advanced fiilter.
I also miss the added value. Well someone who has tested it.

See file for a speedtest.CMR FORM.xlsb