Speedier Alternative To Existing Code

Hi all,

I found a bit of code while trawling Google that deletes a row if it finds a specified value in a specified column (in this instance, "NB" in column E). However, it is very slow and some end users are complaining about the amount of time it takes to run. I was wondering if anyone can suggest an alternative that might be a bit faster. Here's what I'm using at the moment:

Please Login or Register  to view this content.

TIA,

SamuelT

Have you tried turning screen-updating off?

Also, it is said that one should always start at the bottom and work your way up when one is deleting rows. Less stuff gets moved.

Hello SamuelT,

There are a few tricks to speed up the process. First, use a block move to copy the cell values into an array. It is much quicker to access the array elements than the cells themselves. The next trick is create an array of rows themselves using the Union method for a Range object. We can then have the system delete our data without creating a For...Next loop. Here is the revised macro. It uses the methods I just described.

Please Login or Register  to view this content.

If the data is in a table like structure you could use AutoFilter to filter for NB then delete the results. You can record the basic code for this

be sure to post back and let us know how it works out