How to make your Excel VBA code run 1000 times faster
Why Stack Overflow recommends it
Stack Overflow users link it when answering questions about slow Excel VBA loops, as an all-in-one guide to speeding up macros with arrays, no Select calls, and screen updating and calculation turned off.
Recommended by 3 people across 3 different months, 2019 to 2023.
What it is
A 17-minute tutorial from Excel Macro Mastery that takes a typical VBA macro, which reads 100,000 records and copies the rows meeting given criteria, and speeds it up step by step. It covers common optimizations such as avoiding Select, switching off screen updating and calculation, and working with arrays, ending with a run more than 1000 times faster than the original.
What people said
Recommends sTan_The_mAn · 2019-11 · Stack Overflow · on “VBA loop to copy & paste range of specific columns to another workbook multiple times”
You have FAR too many .Select commands, you can tell excel where to write data without them. In addition you need to set the workbooks and worksheets as variables, given that there names are incredibly long, tidying up things. Then you can get rid of the selections with a basic wsTo.Range("B1").Resize(1, Range.Columns.Count).value = wsFr.Range("A16:J1338").value This is just conceptual, you would need to create your range, worksheet, and workbook variables. Also turning off calculations and screen updating will go a long way. I would reference [this video] as an all in one package for speeding up your macros, big or small.
Recommends sTan_The_mAn · 2019-11 · Stack Overflow · on “Is it possible to generate multiple copies of a value based on count data for that value?”
@Benfn121 What do you mean as pertaining to "actually generate the new lines for each fish species"? A basic loop will write your values, row by row, wherever you tell it to. If you wanted more specificity you could utilize a `Case Select` loop. This video will help with speed, if the data is very large link [this video]
Recommends Oran G. Utan · 2023-07 · Stack Overflow · on “Formatting issues in generating multiple docs from a template(mail merge)”
I only thought about the speed (one of my main concerns as I normally have to work with large volumes of data, hence my fixation with arrays...). After I saw this video I changed a lot of my way of coding, I may overuse arrays, but with table kind of data I think they are the best solution [this video]
Recommends DjC · 2023-10 · Stack Overflow · on “How to work with Excel sheet data in an VBA array and then paste back into another workbook”
If interested, check out How to make your Excel VBA code run 1000 times faster [this video] on Excel Macro Mastery’s YouTube channel… Advanced Filter might help speed things up (then sort the results, as needed).