0

My current method for copying A4:M4 into X number of rows is crashing my computer. I am hoping someone might have a VBA loop that is quicker than my current method.

I need to copy A4:M4 into A5:M5,A6:M6....etc until X (rows) is reached) and then copy A5:M5.... etc and paste special back into the same cells. This can be done all in 1 function before moving to the next row.

The X (end of the loop) is defined by cell A2 and is defined by the number of rows in another sheet. My current sheet has 800k rows of data, hence I want to copy a single formula, paste into row, then copy and paste special (so the file size isn't large).

I hope this makes sense.

1 Answer 1

0
' I need to copy A4:M4
Range("A4:M4").Select
' into A5:M5,A6:M6....etc until X (rows) is reached
Selection.AutoFill Destination:=Range("A4:M" & CStr(4 + X)), Type:=xlFillDefault
' then copy A5:M5
Range("A5:M5").Select
Selection.Copy
' and paste special back into the same cells
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone
5
  • Thank you Akina, how do I make the X = a cell reference A2?
    – John
    Jul 5, 2019 at 6:01
  • Also, would a loop speed it up by performing all of the functions in one go before moving to the next line? Also this: ' then copy A5:M5 Range("A5:M5").Select Should be: ' then copy A5:M5,A6:M6.......etc. Range("A4:M" & CStr(4 + X)).Select
    – John
    Jul 5, 2019 at 6:15
  • how do I make the X = a cell reference A2? Use If(IsNumeric(Range("A2").Value), CInt(Range("A2").Value), 4)... and avoid (or add test for) of this value less than 4. The best way is to test and assign to X before AutoFill statement. would a loop speed it up by performing all of the functions in one go before moving to the next line? 99%.
    – Akina
    Jul 5, 2019 at 6:18
  • Hi Akina, any chance you'd be able to re-write it as a loop, as in A5:M5, copy A4:M4, paste into A5:M5, then copy A5:M5 and paste special into A5:M5. (Next) copy A4:M4, paste into A6:M6, then copy A6:M6 and paste special into A6:M6. And loop for the number of iterations in cell A2? (etc. until X rows have been done)
    – John
    Jul 7, 2019 at 23:02
  • Actually, never mind, your solution works quicker than a loop. Thanks heaps.
    – John
    Jul 8, 2019 at 0:16

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .