How can I speed up the macro I created to delete rows based on text in one of the columns

Hi all,

I am new on the forum, so lets start with an introduction. I am Dennis, living in the Netherlands (sorry for my ((d)english ) and working as a data analist. One of the tools I use is excel and I using VBA/Macro's to make my life a bit easier.

At the moment I have created a Macro to delete some rows on tab named "check" when the text in column "I" is not in column "E" at the tab named "B". The Macro is working but it is very slow, due too the large amount of data. Tab "check" haves 100k rows and after the macro 50% will be deleted after running the macro. Tab "B" have 50 rows, so the discision to keep the data is based on 50 factors. I already paused screen updates and calculation to speedup the performance.

See the code bellow, can anybody advise how I can make it faster?

Thanks for your help!

Please Login or Register  to view this content.

Moderator's note: Please take the time to review our rules. There aren't many, and they are all important. Rule #2 requires code tags. I have added them for you this time because you are a new member. --6StringJazzer

If it is only issue that value exists on other list, maybe this way will be a bit faster:

Please Login or Register  to view this content.

Welcome to the forum DBams...Loops are generally very slow especially if you are looping data in the sheet and deleting rows...
It will be advisable to store data into arrays and then loop...
Something like this...Assumes data starts in A1 on sheet Check...

Please Login or Register  to view this content.

If not, please upload a sample file representing your actual file which one could work with...

Upload a sample file in the meanwhile so we can see actual file setup...

Or another option...

Please Login or Register  to view this content.

Hi all -I generated 100000 rows of sample data (strings) and used a timing program to compare everybody's solutions. It's all in the attached workbook.

.SpeedsComp.jpg

As you can see, 3 programs blew away the other 5. All 3 loaded a column with formulas or values to mark rows for deletion, and then sorted on that column to move them to the bottom of the region. With all the "remove" rows together, no "keeper" rows had to be shifted, so deletion was almost instantaneous.

Here's my code. Credit to @veyselemre, whose Range construction I copied:

Please Login or Register  to view this content.

The formula I put in column J (and then resolved to Values):

Formula:

Please Login or Register  to view this content.

This yielded zeros for Keeper rows and Errors for Remove rows. I used these to sort the data, found the Errors w/ SpecialCells, and deleted those rows.

...Deleted...Not Important...

Kudos to you guys...Best I can do incorporating my suggestion in post 3 with my code in Post 10...

'! Here first sorting and then clearing could be faster...

Is 1.97, 1.96, 1.98 with 100K

Please Login or Register  to view this content.

... And we have a NEW Champ (@ 100,000 rows)!!
NOTE: I noticed sintek used Clear instead of Delete. I changed that and re-tested his code. Not much difference; he's still fastest.
NOTE#2 Just realized sintek's code only sorts column I; it needs to sort the whole data region, using column I as the key field. ANOTHER re-test...

Plus, here's my new code. It handles all data sizes fairly well:

Please Login or Register  to view this content.

So, after re-doing my "Check" test sheet to include data in columns A:I (it was just column I), and altering all routines to sort that WHOLE region (sintek):

.CompTimes4.jpg

Here's my test workbook. I had to chop off Archive Data to get it small enough to comply w/ forum rules,
so I added a Workbook_Open procedure to expand the portion I could save. - Lee
ps. Output is still partly manual, but I don't have time to clean that up right now. Feel free to tinker . . . and post your improvements. Thanks.

I have taken off the gloves & rolled up my sleeves :

Please Login or Register  to view this content.

Side note: I move to have @sintek disqualified because altering the arrangement of the rows was not specifically authorized in the OP.

VERY NICE, @johnnyL, you nailed it!

.CompTimes4.jpg

I thought somebody would eventually go with a dictionary object; @Jindon, in particular, has used them often in his posts to good effect, but he seems to have gone dormant on this thread.

ps: I just saw your timings post. You clearly have a faster computer than I; my 2016 Lenovo ideapad 110 is a total potato.

As long as the computer and the excel version and the coding are kept constant for all the testing, The actual timing results don't really matter. When I say that, what i am referring to is the fastest code in one particular setup should be the fastest code in another particular setup. Ie. If the fastest code on your setup completes in 5 seconds, that same code should test the fastest on another computer, it may be slower or faster than your particular timing, but the faster code should be faster, all other things kept equal while testing.

I know I probably didn't explain that properly, but I hope the point is received.

Yes, I'm aware that the testing platform is irrelevant* here. I was just envying a faster machine.

*Note that specific hardware may favor certain processes, and vice-versa. For example, code optimized to run on many-threaded machines will not do well on my 4-thread laptop. Of course, while Excel can be configured to use multiple threads, VBA is always single-threaded. This is one reason I would not be too eager to dismiss the performance of worksheet functions ON THE WORKSHEET.

Originally Posted by johnnyL

...Well that is a debate for another day.

Not just another day, but another thread! We're getting WAY off topic.

@DBams, we hope we've given you a workable solution, and perhaps some food for thought. Welcome to Excel Forums.

If you are satisfied with the proposed solution(s), please go to Thread Tools above your first post and mark the thread as SOLVED.
Also, since you're new to the site, clicking the Add Reputation stars beneath useful posts is a great way to show appreciation
to those who offered help.

sort that WHOLE region (sintek):

OOps...your sample file confused me lol...Replacement of End With and columns(9).sort solves
Glad you guys enjoyed OP's thread...
Happy Coding...

Originally Posted by sintek

OOps...your sample file confused me lol...

Yeah, yeah . . . it was kinda cobbled together, and then I had to pare it down to 1 MB to comply w/ upload limitations.

Originally Posted by sintek

From your first post: ... '! Here first sorting and then clearing could be faster...

Good instinct, it's amazing how quickly Excel sorts. I wish it extended to VBA arrays, too, although coding a sort function is interesting.

Sorry, I am just getting off a night of watching some of my favorite rap videos so I thought I would drop a few bars here:

Let's all welcome kevin from MrExcel
It's his first post here so don't bid him a fare well
He's responding to the post from the OP DBams
So please don't reply with any frikkin slams

I can vouch for the dude, he is great at Excel
Overtime I am sure you will see this as well
With that being said, let's look at the code
And Give the respect that kevin is owed

kevin9999 gets my vote! Simple, robust, and fast!

.CompALL.jpg

Posted by Marc L

Posted by sintek
Here first sorting and then clearing could be faster

After I demonstrated that several times I'm happy someone else at least remind and apply it !

Posted by johnnyL
I think that method of coding has been been proven to be slower in this case.

@johnnyL - to what method are you referring? As the above chart clearly demonstrates, sorting before deletion yields a huge advantage in processing time. That is far and away the single most important conclusion OP should draw from this thread. The rest is minor differences in implementation.
@Marc L - Your implication of ownership notwithstanding, people have been 'sorting before deletion' since VBA was introduced in the 90's!

@kevin9999

Welcome to ExcelForum!

Originally Posted by Marc L

With more than 400-500 K unique items the Dictionary becomes slower and slower...

That might apply if we were removing duplicates (as an example), but kevin9999 (and johnnyL) are just using it
to contain settings (an implicitly short-ish list), not the entire data column.

Originally Posted by Marc L

...So from that day I decided to try to first see what Excel offers first before going to a full looping VBA code …

Agreed. You're not going to beat Excel at its own game. Identify those operations it does best (including some array operations), and let it do them.

Originally Posted by johnnyL

...Side note: I move to have @sintek disqualified because altering the arrangement of the rows was not specifically authorized in the OP.

Just realized what you meant! Sintek's original code left column I out of sync with the rest of the data, but his final version does in fact change the sort order.

@sintek, maybe we can sell it as a "feature", rather than a bug...

@ lee...Already posted reply

OOps...your sample file confused me lol...Replacement of End With and columns(9).sort solves

@sintek - Just kidding around ... no offense was intended.

Question: Your evaluated formula is wrapped in an extra IF clause that returns TRUE for every row. Is that to coerce an array return?

Formula:

Please Login or Register  to view this content.

I ask because occasionally during testing, your code deleted ALL data, implying no array was generated. I'm not sure why; it was very inconsistent. May I suggest using the INDEX function as a more reliable method for coercing arrays:

Formula:

Please Login or Register  to view this content.

Postscript - Please ignore the above, your code seems to run fine on its own; the problem must lie with my test.
If I can't figure it out, I'll start a new thread. I don't want to suck any more air out of this one.

pps: - 6/27/2023 - I eventually realized sintek inadvertently used a local range reference in his Evaluated formula, causing an error if the wrong sheet was active at run time.
The solution was to specify the sheet in the range string (and drop the IF(ROW... ) wrapper; the formula works fine without it):

Formula:

Please Login or Register  to view this content.

@ Lee
None taken...
Not sure how you are populating your formula...I'm guessing the inconsistency is due to the error in the formula...

Formula:

Please Login or Register  to view this content.

Amazing though...Post 41 and OP has still not responded...Also, we have no idea how his actual workbook is setup...

@sintek - is the formula in post#42 the corrected version? I'm guessing yes, since a relative address there doesn't make sense.

BTW, the reason I focused on your code so hard was your interesting use of the EVALUATE method to produce a large array that gets written to the worksheet.
I thought perhaps you might be on to something, but testing proved it's quicker to just let Excel calculate a column and convert it to values.

For that purpose you need a absolute reference E$2:E$51...copy your formula down and you will see the inconsistency in results...
Evaluate on smaller datasets is generally much faster that formula to value conversion and loop...when dealing with less than 2 seconds for code to run...it's user choice I guess...Anyway...interesting thread...so nice to have different available options given to users...
Happy Coding Guys...Till next time...

Originally Posted by sintek

...Evaluate on smaller datasets is generally much faster that formula to value conversion and loop...

Sorry, I know you're ready to move on, but it's not fair to directly contradict what I said in post#43 and not expect a response. I agree that EVALUATE is faster than looping in some cases, but if you're writing the array back to the sheet, it's NOT faster than formula-to-value conversion.

Here's two versions of your program (altered to fix sort). They both put a 1 in column J for matches, and "" for misses. The ONLY difference is, the first writes a formula to the sheet and converts it to values. The second EVALUATEs an array version of the same formula and writes the array to the sheet, as your original code did.

Please Login or Register  to view this content.

.CompareSub5.jpg

3 weeks later . . . just realized I never uploaded the finished version of my Timing Workbook.

Cleaner code, WAY better output (fully automated), plus some notes on data creation.

@MikeVol - Your code in post#47 doesn't even mention Sheet("B"); it merely deletes all rows containing instances of BigLongString------------------------------------NOT FOUND, a stand-in value intended to represent any entries not found on the list.
How does that meet @DBams original requirement to verify membership for all entries?

Just to be thorough, I did test BOTH your code submissions (post#16, post#47):

.TimesMikeV.jpg

Your code reads individual cells in a loop, which is VERY SLOW. In general, may I suggest reading entire ranges into an array - a single operation - and looping thru that, as @JohnnyL did in post#5.