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
与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…