Fastest way to copy/paste values large range

  • Code below is slow and gives error (closed the dialog box, don't want to get stuck again, but it was on last line)
    Need fast performance on 20000 + rows
    Trying to just copy column with formulas and paste as values in same place


    Code
    1. Sub FastPaste()
    2. Dim r1 As Range, r2 As Range
    3. Set r1 = Sheets("Sheet1").Range("G1:G20000")
    4. Set r2 = Sheets("Sheet1").Range("G1:G20000")
    5. r1.Copy r2.Value
    6. End Sub
  • Hello,


    Have you tried :


    Code
    1. Sub Test()
    2. Sheets("Sheet1").Range("G1:G20000").Value = Sheets("Sheet1").Range("G1:G20000").Value
    3. End Sub

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Like" icon ...just below...in the bottom right corner...:)

  • I'd also say something nice... but sometimes that may be taken as spam, I never know
    But seeing how I am already saying something, I'll dare to say:
    Thank you for your solution - worked in an instant!

  • I'd also say something nice... but sometimes that may be taken as spam, I never know
    But seeing how I am already saying something, I'll dare to say:
    Thank you for your solution - worked in an instant!


    Glad to hear the ' value solution ' is fast enough for your 20'000 rows ...:wink:

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Like" icon ...just below...in the bottom right corner...:)