Automate Copy / PasteSpecial

[Guest]

I have about 40,000 lines in my spreadsheet. For a given column, say column
k, can a macro copy/paste special/values in 1000 row blocks ?

In other words. Starting a K4, copy k4:k1004, paste special / value over
the same range. Then copy k1005:k2000, paste special / value over the same
.. Continue to do this until the last line of data (or until a specified row).

Thank you in advance.

 

[Guest]

To do a single block of 1000 cells:

Sub Macro1()
Dim r As Range
Set r = Range("K4", "K1004")
r.Copy
r.PasteSpecial Paste:=xlPasteValues
End Sub

and rather than loop 100 cells at a time:

Sub Macro2()
Dim r, r2 As Range
Set r2 = Range("K4").End(xlDown)
Set r = Range("K4", r2)
r.Copy
r.PasteSpecial Paste:=xlPasteValues
End Sub