Copy Formula Down in VBA

So far, you are probably aware that Visual Basic for Applications (VBA) can be used to automate basically anything we work on in Excel.

With VBA, we can speed up our processes. We can use it to copy the formula that we make to the end of our table, or list, regardless of its length. We will show how to do it in the example below.

Copy Formula Down in VBA

For our example, we will use the list of NBA players and their statistics from one game in several categories: points, rebounds, assists, and turnovers:

Now let us assume that we want to sum up the points, rebounds, and assists that every player had. We will insert this formula in cell H2, and then drag this formula to the end of our table. These actions will be done in VBA.

To open the VBA, we will click ALT + F11, on our keyboard, then go to the Insert tab, and choose Module:

On the window that appears on the left side, we will insert the following code:

This code first declares one variable, variable r as long (a number) and then it defines this variable to be equal to the count of cells that are filled in the first column (column A). In our case, that will be number 10.

Then we add a formula to cell H2 that is equal to the sum of points, rebounds, and assists. For the final step, we will autofill the range in column H with the formula in cell H2 until the number of rows that are defined in our variable r, which is number 10 (number of rows in our table).

We can execute the code by pressing F5 while in the module, or we can also choose to press F8 to step through the code. By doing so, we can inspect every line of the code. When we get past defining variable r, we can see that it is set to number 10:

When we go further along the way by pressing F8, we will see that we will have the formula created in our cell H2:

Then, in the next and final step, we will have our whole range filled with the formula: