Speeding up a macro

[Mister P]

I'm using a worksheet where 2 columns ( E and N ) are used to determine whether to delete rows. Column E will contain values "S", "H" or "D". Column N contains numeric values from 0 to 150000.

The macro sample works perfectly when several hundred rows are used but really slows down when rows increase to several thousand. Forty thousand rows are commonly encountered and elapsed time is 7 mins.

Any assistance to speed up this macro would be gratefully appreciated.

Please Login or Register  to view this content.

[jaslake]

Hi Mister P

I should think you could use filtering on this. If you'd post a sample of your file, I'll be glad to look at it.

Jon

[johnjohns]

You can use

Please Login or Register  to view this content.

rgds

johnjohns

[Mister P]

Tried that, JohnJohns. No improvement. Actually the macro ran slower.

Mister P

[Mister P]

I'm enclosing a small sample worksheet. The macros in this sample run quick. My full worksheet has over 46,000 rows and really slows down.

Thanks for looking.

Mister P

[MarvinP]

Hi Mr P,

Try this.

Please Login or Register  to view this content.

The idea is to select all the rows first and then only do a single delete. You may need the "EntireRow" and xlUp to make it work correctly.

hth.

[jaslake]

Hi Mister P

If you haven't a solution by then, I'll get back to you in the AM. Getting late here.

John

[shg]

I think jaslake's filter suggestion is the best idea.

[Mister P]

Marvin P
Tried your code in my full worksheet. It works the same as the code I used and didn't gain any time. Both codes ran 4.3 mins duration. In actual use I have to run the macro 3 times which totals to 17 mins.

I'm wondering if there is a different way to apply the loop.

Thanks

Mister P

[MarvinP]

Mister P,

I believe the looping through all the rows is the real problem. Have you tried Advanced Filters?
Do a search for "Excel Advanced Filter" or start reading http://www.contextures.com/xladvfilter01.html

[Mister P]

Marvin P
I will look into the filter method. I have absolutely no knowledge with this.

Thanks, Mister P

[royUK]

Try this

Please Login or Register  to view this content.

[Mister P]

royUK
I will try your code later today. Appreciate your help. Will let you know how it works on the large worksheet.

Finished a test run of your code with very promising results. Is there a way to add an OR agrument so blank cells in col N also trigger a deletion of those rows as well.

In other words, a row which contains S in col E, <2000 in col N or blank in col N would be deleted.

Thanks

Mister P

[royUK]

Try this

Please Login or Register  to view this content.

[Mister P]

royUK
I applied your filter code to my application. I added a feature to my code which changes cells which are blank to a zero value. This allowed your code to provide the exact results I required with an exceptional speed improvement. My loop code was 16.5 mins duration. Your filter code was 2.4 mins duration.

Appreciate the help

Mister P

PS, Tested your modified filter code and it works perfectly without having to add zero's to blank cells.

[royUK]

Glad it worked & it also proved that it's best to avoid loops whenever possible

[snb]

or

Please Login or Register  to view this content.

[royUK]

No error handling will result in problems if no cells match the SpecialCells criteria

There might be more data on a sheet than the actual table, in which case UsedRange is not appropriate.