# VBA Formula Filldown 10 Columns at a time until last column

#### shane_aldrich

##### Board Regular
I use the code below filldown formulas, it works great, but takes forever. There are too many columns/rows to filldown the entire range without me getting a memory error, so instead, I'd like the second "i" in each of the formula ranges to be i+10

i+10, so it would fill down 10 at a time, but I'm not dealing with increments fo 10, so I'm not sure how to handle when i+10 exceeds lLstFormCol...so if i+10 exceeds lLstFormCol, do i+lLstFormCol

Sub DTR_Raw_04_Formulas()

'*************************
'*** Declare Variables ***
'*************************
Dim ws1 As Worksheet
Dim lLastRow As Long
Dim lLastCol As Long

Dim lFstFormCol As Long
Dim lLstFormCol As Long
Dim rForm1 As Range
Dim rForm2 As Range
Dim rForm3 As Range

Dim rng1 As Range

'*********************
'*** Set Variables ***
'*********************

Set ws1 = Sheets("Raw_DTR")
ws1.Activate

lLastRow = Last(1, Columns(1))
lLastCol = Last(2, Rows(1))

lFstFormCol = 11
lLstFormCol = 99

'***************
'*** Execute ***
'***************

'Formulas Filldown
For i = lFstFormCol To lLstFormCol

Set rForm1 = Range(Cells(4, i), Cells(4, i))
Set rForm2 = Range(Cells(4, i), Cells(lLastRow, i))
Set rForm3 = Range(Cells(5, i), Cells(lLastRow, i))
rForm1.Copy Destination:=rForm2
ws1.Calculate
rForm3 = rForm3.Value
Next i

End Sub

### Excel Facts

How to find 2nd largest value in a column?
MAX finds the largest value. =LARGE(A:A,2) will find the second largest. =SMALL(A:A,3) will find the third smallest
Approximately how many rows and columns of data do you have in Sheets("Raw_DTR")?

What is the formula that you are copying from Cell K4?

You should be able to apply the formula using Range.FormulaR1C1 without the need for the intervening ws1.Calculate.

i figured out the loop part, but is that because the value function recalculates before it converts them to values, the reason I ask is because I was watching the code and it did look like it was calculating twice?

If you can answer the questions in my first reply, I'll try to help.

Replies
2
Views
138
Replies
1
Views
239
Replies
10
Views
286
Replies
6
Views
235
Replies
4
Views
308

1,207,260
Messages
6,077,351
Members
446,279
Latest member
hoangquan2310

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

### Which adblocker are you using?

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

### Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back