chris priyesh
New Member
- Joined
- May 10, 2014
- Messages
- 7
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
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
Code:
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