Copy Formula down to last row using FillDown method in VBA Excel

copy fomula down filldown vba excel

Objective

To copy formula down to last populated row in a column by using FillDown method in VBA Excel.

Approach

In this VBA excel automation ,we had data in column A and column B in 10 rows. For demonstration purpose we have applied the formula A1*B1 in cell D1. Now, we aim to copy the formula till last populated row, which is row 10. So, we have used the FillDown method of the range object.

Syntax for the FillDown method is shown below –

expression.FillDown

Here Range will be the expression. We can see that in the code below.In the code below, first we have found the last populated row. Then we have applied the FillDown method on cell D2 to D10.Here whatever formula we have applied in the cell D2, that will be copied till cell D10.

 

Code

Sub CoopyFormulaDown()
Dim LastPopulatedRow As Long

'Find last populated row
LastPopulatedRow = Range("A" & Rows.Count).End(xlUp).Row

'Select the rows where formula is to be populated
Range("D2: " & "D" & LastPopulatedRow).FillDown

End Sub

 

 

In the image below we can see that we had formula in cell D2 only, and cell D3 to D10 were empty.

 

copy fomula down filldown vba excel

 

 

After executing the above shown code, formula (i.e. A2* B2) is copied till cell B10, which is the last populated cell.

 

copy fomula down filldown vba excel

 

We can adopt similar approach for any formula in a particular cell.This method is very helpful when we are automating a excel dashboard.

 

Reference

https://docs.microsoft.com/en-us/office/vba/api/excel.range.filldown


Post you may like

How to change the name of shapes on slide in PowerPoint VBA