VBA to store rows in different arrays based on cell values

[AndersThyme]

Dear all,

Thank you for helping out. I have searched the forum for other threads that could assist, but did not find any, so here we go.

I have an Excel workbook with 6 different macros. Each macro searches row by row to find values, and with each hit it copies the row to the correct sheet.

An example of such macro:

Please Login or Register  to view this content.

Because of roughly 60,000 rows, this coding is slow, and crashes often. And, to add to that, I have 6 different macros that copies to 6 different sheets based on different criterias. E.g. Criteria1 to 10 goes to sheet1, Criteria11 to 15 goes to sheet2 and so on.

Therefore, my question is if it is possible to create an array instead? (other ideas are also more than welcome).

I was hoping that it would then be possible to do the following through 2 macros:

Macro 1:
Loop through all rows and store rows with Criteria1 to Criteria10 in Array1, Criteria11 to Criteria15 in Array2, Criteria16 to Criteria20 in Array3 and so on.

Macro 2:
Copy all rows from Array1 into sheet1
Copy all rows from Array2 into sheet2
and so on.

Thank you so much in advance. I am new to the array functions, and would really appreciate some help.

Kr Anders

[jindon]

Loop through all rows and store rows with Criteria1 to Criteria10 in Array1, Criteria11 to Criteria15 in Array2, Criteria16 to Criteria20 in Array3 and so on.

*Criteria1* should include Criteria10 to Criteria19, Criteria100 to Criteria199 etc.

So, I don't think your code work properly.

[AndersThyme]

Hi Jindon,

Sorry, that is my bad for choosing a bad example.
I have changed the actual criterias due to not being able to share the originals.

But the criterias will be changed to actual values, which could be e.g. names: "*Anders*", "*John*" and so on.

Kr Anders

[jindon]

Then need to see how the list of criteria constructed, but I guess AdvancedFilter would be the way.

[AndersThyme]

Hi again,

I could not find out to do advanced filters for this search, since I do not have the exact values, but have to search on a "contains" level. E.g. the value might be "AndersMark" but should be found searching for "*Anders*" only.

The current working (slow) macros:

Macro1:

Please Login or Register  to view this content.

Macro2:

Please Login or Register  to view this content.

Macro3:

Please Login or Register  to view this content.

Macro4:

Please Login or Register  to view this content.

Macro5:

Please Login or Register  to view this content.

Macro6:

Please Login or Register  to view this content.

[jindon]

Looping 60,000 rows would take time.
You can use AdvancedFilter with the wild card for multiple criteria in one go.
If you upload a small sample workbook with the criteria, I can show you how.

Attach a sample workbook (not a picture or pasted copy). Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

[AndersThyme]

Hi again,

Thank you for all the help, I really appreciate it.

I have attached an example where the code I use currently is in, and is working (see Module 1). So you can see what the desired output is but running the macros (I have already run all the macros, to have the desired output shown in the different sheets).

I hope that gives the best overview :-)

Kr Anders

[jindon]

See if this runs faster.

Please Login or Register  to view this content.

[AndersThyme]

Hi Jindon,

Wow, it works like a charm on the test sheet.
I will update it with 'sensitive' data and test it on the full data set and get back to you to confirm if it works.

Kr Anders

[jindon]

OK.
As you can see each elements in an array consists of Array.

e.g

Please Login or Register  to view this content.

1st element which is Array of criteria, Array("*John*", "*Niels*")
and the 2nd element, "John" is a sheet name to correspond to.

[AndersThyme]

Hi Jindon,

I have now tested it with >55,000 rows and it works perfectly. It has reduced the time from 15-20 minutes to take < 1 minute.

Thank you for the patience, the help and the very useful outcome. It is really appreciated.

Kr Anders

[jindon]

You are welcme and thanks for the rep.

It would help to speed up if you set Application.ScreenUpdating = False and True at the end. e.g

Please Login or Register  to view this content.

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.

[AndersThyme]

Thank you again :-)