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

[DBams]

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

[KOKOSEK]

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.

[sintek]

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...

[windknife]

Maybe, merge all rows into range, then only delete once .

Please Login or Register  to view this content.

[johnnyL]

Without any data to test with, the following is what I came up with, in my head:

Please Login or Register  to view this content.

This is normally a very fast approach.

[jindon]

Set rng1 = ws.Range("I2:I" & lastRow1)

Assuming I1 has header.

Please Login or Register  to view this content.

[DBams]

Thank you all for your answers, looks really helpfull. Can't wait to try, unfortunatly I have a very busy day today so I will try these solutions next week and let you know.

[sintek]

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

[veyselemre]

Alternative;

Please Login or Register  to view this content.

[sintek]

Or another option...

Please Login or Register  to view this content.

[leelnich]

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):

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.

[sintek]

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

[johnnyL]

I would have to agree with the results from @leelnich, but I would like to add a footnote ... the script I provided is built for stamina, in other words, the ROR (Rate of Return) remains fairly constant with increasing amount of rows.

On my testing computer, the code I submitted, with the testing data submitted, processed about 100k rows / second. The other codes submitted that used formulas, rapidly fall off on the timings as the amount of rows being tested increases. Ie. Try 500k results ;

The op in this thread clearly stated 100k rows being tested & the submission winner, thus far in this thread, goes to @leelnich. !!!

Another side note, @leelnich, I normally test the timing of scripts multiple times & then remove the highest recorded time as well as the lowest recorded time & then get an average of the remaining values.

[sintek]

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.

[leelnich]

... 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.

[MikeVol]

Hello @DBams
Another option according to your first post.

Please Login or Register  to view this content.

[leelnich]

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.

[johnnyL]

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.

[johnnyL]

Results from my most recent testings:

[leelnich]

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.

[johnnyL]

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.

[leelnich]

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.

[johnnyL]

VBA is always single-threaded?

Well that is a debate for another day.

[johnnyL]

You are correct in essence, but possibly don't know about workarounds to 'simulate' multithreading. Google Asynchronous and Daniel Ferry.

[leelnich]

...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.

[sintek]

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...

[leelnich]

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.

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.

[Marc L]

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 !

[johnnyL]

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

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

[kevin9999]

I note this thread still hasn't been marked as "solved" yet, so I thought I might use it as my first post on this site since becoming a member. I used leelnich 's sample file in post #11 as the basis. First post so I hope I don't stuff it up.

Please Login or Register  to view this content.

[johnnyL]

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

[johnnyL]

I believe we have a new champion!

Attachment 833896

[leelnich]

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!

[AliGW]

@kevin9999

Welcome to ExcelForum!

[kevin9999]

Thank you AliGW, leelnich & JohnnyL

[Marc L]

With more than 400-500 K unique items the Dictionary becomes slower and slower
so for big / huge data it's not the way to go, it should be replaced with a Collection
or better just using array(s) when that's possible …

[Marc L]

@Marc L - Your implication of ownership notwithstanding:rolleyes

You misread or misunderstood …
As for years within this forum I've never have seen some helper sharing a VBA procedure following 'the kid way' (1)
so one day I posted a code changing from the usual answers …
Few days later, another thread with a similar subject, the same helpers sharing their same slower ways …
So I shared again the same way … The same during months, maybe years …
So now you should be able to understand why I'm happy with Sintek's post !

What is the best code ? The faster ? Maybe if it's a speed challenge
but usually the best code could not be the faster but the one the OP can maintain himself …

(1) Why I called it 'the kid way' ?
'cause one day in a company the team had to attend to a presentation with its author,
you know the kind of guy when there is an issue he's never involved
but when something works that's thanks to him even if he never worked on the project,
so proud he was with its last project (so boring it was in fact)
and at the end he launched via a button a 'cleaning' VBA procedure removing rows
which was lasting for ages, more than ten minutes, so the director which was the final user
became red and started to shout how unhappy he was !
Then the miracle started : we heard a little young voice arguing
« I don't understand it can be so long as manually that should not take no more than two minutes, maybe one ! »
This voice was the one of a 'summer job' young guy. So the director asked for a demonstration
and the 'kid' did the same than the VBA procedure within a minute, all operating manually !
So from that day I decided to try to first see what Excel offers first before going to a full looping VBA code …

[leelnich]

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.

...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.

[leelnich]

...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...

[sintek]

@ lee...Already posted reply

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

[leelnich]

@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?

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:

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):

Please Login or Register  to view this content.

[sintek]

@ Lee
None taken...
Not sure how you are populating your formula...I'm guessing the inconsistency is due to the error in the 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...

[leelnich]

@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.

[sintek]

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...

[leelnich]

...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

[leelnich]

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]

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

Okay. What about this code? The code written is not mine, I say right away.

Please Login or Register  to view this content.

[leelnich]

@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.