To copy formula down to last populated row in a column by using FillDown method in VBA Excel.
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 –
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.
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.
After executing the above shown code, formula (i.e. A2* B2) is copied till cell B10, which is the last populated cell.
We can adopt similar approach for any formula in a particular cell.This method is very helpful when we are automating a excel dashboard.
Post you may like