MegaSack DRAW - This year's winner is user - rgwb
We will be in touch
Is their any way, missing comments etc. to get a vba script to run a bit quicker? The one I have is reasonably quick but because of the size of the worksheet it is taking a while!
There could be, but it depends on what the code does!
It's like asking how could you make a novel shorter.
By far the cheapest approach is to buy a better PC (assuming this isn't for personal use).
Get a better vba programmer...
Get a better computer...
Turn off everything else on your computer.
Probably in that order to.
It is a two condition lookup, find match in column one, look in column two for a second match. Small detail is comparing two tables with 10000 rows each. I need to find rows that don't match.
How are you doing it?
That sounds like it could be significantly optimised. How are you looking for things?
Oh, and you say 'tables' - I presume you mean worksheets?
Don't delete comments!
Turn off screen updating while you do the heavy lifting (and turn it on again afterwards).
2nd turning off the screen updating, seems to slow things right down. (to me) it always looks magical when the end thing suddenly appears on the screen 🙄
can you do it in Excel, rather than VBA?
Sounds like 2 vlookups would work. But I think 2 collections in VBA would work quicker.
Junk the looks ups, use the vba to run down the columns until it finds a match.
Another way of massively speeding up the code in a large workbook is to switch off automatic calculation when the macro is running. Obviously if the macro is using the results from a work book formula this doesn't wotk so well although you can get the VBA to calculate the results for a specific formula as part of the code. Don't forget to turn the automatic calculation back on at the end of the macro. If the macro crashes don't forget to turn it back on manually.
its faster to use worksheet functions, called from VBA, than to implement them yourself eg worksheetfunction.vlookup etc