VBA to use different function other than looping to make code run faster

[kosherboy]

I recently got introduced to coding with filtering which if designed correctly can do most of the work certain looping codes do and would result in faster processing.
So here is a code i use with looping:

Please Login or Register  to view this content.

This code takes forever since there is a lot of information the code needs to loop through.
Since i only got introduced to filtering i don't have any idea on how to convert the above code to work via filter.

These are the steps in the code above written out:
1) The code enters sheets "Pending Orders"
*might be helpful to note that the information on "Pending Orders" starts from row 5 and row 4 is the header, also the part of the code that i need copied is every odd row but vital information pertaining to every odd row exists on the row beneath it which is an even row.
2) The code finds the word "ORDER CANCELLED" which will exist on only an even row
3) If the code finds "ORDER CANCELLED" it will then move to the odd row right above it and copy this:

Please Login or Register  to view this content.

4) Next the code enters sheets "Report Center" and pastes the copied information into row 5 and so on, meaning it copies the first loop match into row 5 and the 2nd loop match into row 6 and the 3rd into row 7 and so on and so forth.

Any help on making this work with filtering would be greatly appreciated.

[kosherboy]

"Hold off! There is a slight mistake in my code, i will edit now...."

(a couple minutes later....)

"Ok, fixed now"

[StuCram]

Hi

Tip 1:
I think that the UNION( ) statement could omit the "ws2" references since you already have a "With ws2" stamens earlier.
This may be a bit faster:

Please Login or Register  to view this content.

Tip 2:
Switching to use CELLS() rather than RANGE() might be more efficient as it avoids those string concatenations;
it may be worth investigating.

Please Login or Register  to view this content.

[StuCram]

And here's Tip 3:

Change the FOR loop to use the STEP 2 option so it counts by 2 automatically.
Then you do not need the IF rcell MOD 2 = 1 calculation to determine odd row #s

Please Login or Register  to view this content.

[mikerickson]

Rather than

Please Login or Register  to view this content.

You could use

Please Login or Register  to view this content.

Rather than

Please Login or Register  to view this content.

you could use

Please Login or Register  to view this content.

[/CODE]

[kosherboy]

And here's Tip 3:

Change the FOR loop to use the STEP 2 option so it counts by 2 automatically.
Then you do not need the IF rcell MOD 2 = 1 calculation to determine odd row #s

Please Login or Register  to view this content.

Thank you for those tips, however the code still took the same amount of time to run.

[StuCram]

And here's tip # 5

Set a range to the initial group of cells to be copied
Then in the loop, use the OFFSET() method to adjust it quickly to the desired group on the new row.
New / modified lines of code are shown in bold below.

Please Login or Register  to view this content.

[kosherboy]

And here's tip # 5

Set a range to the initial group of.....

Thank you again.
The debugger went on and the ".range" got highlighted:

Please Login or Register  to view this content.

With this message:
"Compile error:
Wrong number of arguments or invalid property assignment"

[StuCram]

OOPS - my carelessness. Far too many quotation marks.
I also noticed a small typo by me: the A5 near the end of the statement should be AA5, I think.
Try this change:

Please Login or Register  to view this content.

or this shorter form (incorporating tip from Mike Erikson)

Please Login or Register  to view this content.

[kosherboy]

I appreciate all your help but i still don't see any difference in the timing for the code to run

[kosherboy]

Was wondering if it's possible to use the filter?

[StuCram]

Those previous tips have helped in some of my projects but of course every situation is different.

I don't have much experience (yet) using filters.
That usually speeds up many tasks but here you are working with 2 rows at a time so filtering may not work.
Sigh.

Hope you find a solution and share it.

[kosherboy]

Ok, thank you once again for your time on this

[jaslake]

Hi kosherboy

How about posting a Sample of your Data so we can play with it.

[sktneer]

Try this to see if this helps...

Please Login or Register  to view this content.

[kosherboy]

Dear sktneer,
There is definitely a noticeable change in speed with the filtering
I appreciate your code very much

[sktneer]

You're welcome. Glad I could offer some help.
Thanks for the feedback as well.