How to improve time on copying last row n times below data + leave last row as formula

Hi dear comunity! I am pretty new on the VBA world.

I am currently trying to achieve something, and i am not sure how to do it.


I have a worksheet in an excel file that has x rows and y columns (it changes across time). The last row is all formulas (not pure values).

I am currently trying to:

1. find last row
2. copy that row
3. paste n times below (n is defined before and is never more than 15-30).
4. Then copy and paste all the data as value LESS last row.

So far I've done this, but it gives me the impression that with the iteration in i is pretty slow....

Dim lRow As Long
Dim i As Integer

'n is defined above

For i = 1 To n
lRow = Ws.Cells(Rows.Count, 1).End(xlUp).Row

Ws.Range("A" & lRow).EntireRow.Copy Ws.Range("A" & lRow + 1)

Next i


Is there a way to do it directly? without iteration? just copy n times? I also didn't find much info on how to keep all the rows as value less last one.

Could you guys pls help? thanks in advance!

try such approach:

Please Login or Register  to view this content.

PS. Note how CODE tags improve readability