AutoFill Data Using Excel VBA
|Home||More Excel (VBA) Training Videos|
|| 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.
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:
'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 smae 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: