Tom’s Tutorials For Excel: Fill Formula to Last Used Row
The picture on the left shows a list of numbers in column A starting in cell A2. You have a formula in cell B2 which you want to quickly copy down to the last used cell in column A.
With cell B2 selected, position your mouse directly over the fill handle (that tiny black square in the lower right corner of the active cell) until the mouse cursor looks like a crosshairs, as pointed at by the red arrow. Double-click the left mouse button to fill the formula down as shown.
In a macro, you can declares a variable for the last used row in column A and fill the formula in cell B2 down to the corresponding cell in column B.
The example formula as shown in the address bar in the picture is
The VBA equivalent of that formula is
=RC1+1 so a macro could be:
Sub FormulaFill() Dim LastRowColumnA As Long LastRowColumnA = Cells(Rows.Count, 1).End(xlUp).Row Range("B2:B" & LastRowColumnA).Formula = "=RC1+1" End Sub