What is the most efficient/quickest way to loop through rows in VBA (excel)?

by

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.

Dim i As Long
Dim varray As Variant

varray = Range("A2:A" & Cells(Rows.Count, "A").End(xlUp).Row).Value

For i = 1 To UBound(varray, 1)
    ' do stuff to varray(i, 1)
Next

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.

Sub test()

Dim varray As Variant
Dim i As Long

varray = Range("A2:A10").Value

'must step back or it'll be infinite loop
For i = UBound(varray, 1) To LBound(varray, 1) Step -1
    'do your logic and evaluation here
    If varray(i, 1) = "foo" Then
       'not how to offset the i variable 
       Range("A" & i + 2).EntireRow.Insert
    End If
Next

End Sub

More Related Contents:

  1. Vba macro to copy row from table if value in table meets condition
  2. Change length of For loop while in the loop
  3. delete Autofilter Field resullt using the header Name
  4. How can I call a macro which contains a loop but only run one iteration of that loop?
  5. Somehow it’s not copying [closed]
  6. VBA macro that search for file in multiple subfolders
  7. Expanding column cells for each column cell
  8. So, I have 6 “master” files to then divide into 40 separate files
  9. Getting ScriptControl to work with Excel 2010 x64
  10. Split comma separated entries to new rows [closed]
  11. Application.Match gives type mismatch
  12. Count and Highlight keywords within phrases
  13. Transpose a range in VBA
  14. Protecting cells in Excel but allow these to be modified by VBA script
  15. Select first empty cell in column F starting from row 1. (without using offset )
  16. Excel VBA – QueryTable AfterRefresh function not being called after Refresh completes
  17. Excel VBA: AutoFill Multiple Cells with Formulas
  18. VBA.Find and adding a value
  19. excel vba- extract text between 2 characters
  20. Round function in Excel, worksheet function vs VBA
  21. How to continue the code on the next line in VBA
  22. Excel VBA Userform – Execute Sub when something changes
  23. Count the number of rows in another sheet [duplicate]
  24. Getting Excel to refresh data on sheet from within VBA
  25. VBA Password Input with Cancel Function
  26. Finding first blank row, then writing to it
  27. Run-time error ’91’ (Object variable or With block variable not set)
  28. How to have vba execute every 10 minutes?
  29. Read and write from/to registry in VBA
  30. Unable to read data from a CSV using ADO due to the driver thinking I am working with integers/number and showing nulls instead of text

Leave a Comment