Need to optimize a slow vba code

Question

I am using following code and it is taking forever to complete-

For i = 1 To 21408
    Range("abcd").Copy
    Rows(Range("abcd").Offset(i, 0).Row).Insert Shift:=xlDown
    Application.CutCopyMode = False
Next i

abcd is Named Range having 8 fields.

Can someone please suggest a way to optimize this code or re-write in a way to achieve the same output?

Thanks

Doug Glancy · Accepted Answer · 2013-07-31 01:02:30Z

3

This does the same thing, much faster:

With Range("abcd")
    .Copy
    .Offset(1).Resize(21408, .Columns.Count).Insert Shift:=xlDown
    Application.CutCopyMode = False
End With

answered Jul 31, 2013 at 1:02

Doug Glancy

27.5k6 gold badges72 silver badges118 bronze badges

Sign up to request clarification or add additional context in comments.

1 Answer 1