Copy and paste on loop for a set range

[chris priyesh]

Dear Experts, need your help.

I am working on a huge excel file with over 50k rows. I have a formula in Cell L2. If i use filldown, the excel is taking too long to process. My idea is to copy the formula in L2, paste it in the remaining cells of column L (i.e., from L3 to the end), then copy and paste the cells from L3 to the end as values.

Considering the speed, i wanted to do it on a loop for every 500 Cells. The steps would be as follows:

Step 1: Set Range based on Column A
Step 2: Copy formula in L2
Step 3: Paste in L3 : L503
Step 4: Copy L3:L503 and paste special -values in L3:L503
Step 5: Copy Formula in L2
Step 6: Paste in L504 : L1004 ...
Continue till range is filled.

I came up with the following Code. But the code is not dynamic and is not working. can you guys help me. Thanks in advance

Sub Fill()
Dim i As Long
For i = 1 To Range("a1", Range("a" & Rows.Count).End(xlUp)).Value
Range(B2).Select
Selection.Copy
 Range("B2:B" & i).Select
    Selection.FillDown
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Application.CutCopyMode = False

Next i


End Sub

 

[alansidman]

Eliminate the Calculate lines in the code and only recalculate after the last copy and paste has been completed. See if that makes a difference