We know how to Autofill data in an Excel worksheet using standard procedures. Once we calculate the amount in the first row by multiplying ‘unit price’ with the ‘quantity’ of the first item, we need not write the formula again to calculate the amount in the next row. We select the cell holding the value of the amount, click and drag on the black dot in the lower right corner of the cell as soon as our mouse cursor becomes a black cross. In case we have thousands of rows of data performing ‘click and drag’ can be cumbersome. Here we just double-click on the black dot and the calculations in the rows below is automatically completed.
Now how can we do the same kind of Autofill using Excel VBA which offers obvious advantages of automation.
Writing the macro code:
Click on the developer tab
In the code group select visual basic
The Microsoft Visual Basic for Applications window opens
In the workspace write the code as shown below
Code for the Autofill macro:
‘First we define the macro name using the keyword sub for subroutine
‘The cell d2 will be assigned the value from the product of cell value in the same row and two columns away (cell B2) and the cell value in the same row and one column away (cell C2) from the cell where you are entering the formula
Range(“D2”).Value = “=RC[-2]*RC[-1]”
‘Next we select the cell D2 which has the result
‘From the cell D2 we do an Autofill upto the cell we wish to by defining the first cell to the destination cell like D8
You can, in fact, record your macro, view the macro code and tweak it a little bit to achieve the same result as we have done by writing the macro in the VBA editor. Later on you’ll learn that you can also attach this macro code to a command button and make the process even more user-friendly.
Watch the Excel training video below to see how the macro can perform the autofill action quickly and easily: