Fill or copy down

Fill or Copy down formulas within a row


Fill down based on another column length

U2 is the cell that has the formula being copied; column A is where the length is determined. I chose to use the 500th row as the range to begin looking up from to get to the last row. This could just as easily be 65,536, or 1,048,576.

[sourcecode language=”vb” light=”true” wraplines=”false” gutter=”false”]
Sub CopyOne()
Selection.AutoFill Destination:=Range("U2:U" & Range("A500").End(xlUp).Row), _
Type:=xlFillDefault
End Sub
[/sourcecode]


Fill Down (Copy Down) Based On Cell Number

Two Different Ways:
This is a macro to fill Sheet1 cells A1 through P1 down, based on the number in Sheet2 cell R1.

Fill Down (option 1)

[sourcecode language=”vb” light=”true” wraplines=”false” gutter=”false”]
Sub Macro1()
Range("A1:P1").AutoFill Destination:=Range("A1:P" & Sheets("Sheet2"). _
Range("R1")), Type:=xlFillDefault
End Sub
[/sourcecode]

Fill Down (option 2)

[sourcecode language=”vb” light=”true” wraplines=”false” gutter=”false”]
Sub Macro2()
Range("A1:P1").Copy Range("A2:P" & Sheets("Sheet2").Range("R1"))
End Sub
[/sourcecode]

Fill Down Values, not Formulas
The following code will fill down a formula as values, not live formulas, and in this case will give the result a percentage with 1 decimal number format.
[sourcecode language=”vb” light=”true” wraplines=”false” gutter=”false”]
Sub Macro3()
Dim LastRow As Long
With Sheets("budget")
LastRow = .Range("A" & Rows.Count).End(xlUp).Row
With .Range("L2:L" & LastRow)
.Formula = "=M2 + N2" ‘add the correct formula here
.Value = .Value
.NumberFormat = "0.0%"
End With
End With
End Sub
[/sourcecode]