I know VBA in Excel isn't the quickest of things – but I need the most efficient (i.e. quickest) way to loop through a large sample of rows.
Currently I have:
For Each c In Range("$A$2:$A$" & Cells(Rows.count, "A").End(xlUp).row
' do stuff
Next c
The 'do stuff' includes insert a row here and there (so I need to keep the dynamic lookup of the range.)
Any ideas (looking at 10,000 rows+)?
EDIT
I am already using
Application.ScreenUpdating = False
Application.Calculation = xlManual
Best Answer
If you are just looping through 10k rows in column A, then dump the row into a variant array and then loop through that.
You can then either add the elements to a new array (while adding rows when needed) and using Transpose() to put the array onto your range in one move, or you can use your iterator variable to track which row you are on and add rows that way.
Here is an example of how you could add rows after evaluating each cell. This example just inserts a row after every row that has the word "foo" in column A. Not that the "+2" is added to the variable i during the insert since we are starting on A2. It would be +1 if we were starting our array with A1.