How to Apply Formula to Entire Column Using Excel VBA

In this tutorial, I am going to show you 3 simple methods on how to apply a formula to an entire column using excel VBA. These methods will save a large portion of your time. Also throughout this tutorial, you will learn some valuable excel VBA commands and tools which will be useful in other excel related tasks.


Download Practice Workbook

You can download the practice workbook from here.


3 Simple Methods to Apply Formula to Entire Column Using Excel VBA

We have taken a concise dataset to explain the steps clearly. This dataset has 2 columns and 6 rows. The two columns are Product Price and an assumed Tax at 3%. Also, note that we have formatted all the data cells in the Accounting format. Besides, we will save the excel workbook as a Macro-Enabled file format as we will be running VBA codes in all the methods.

apply formula to entire column excel vba


1. Using Range Formula in VBA

We can define the entire range of cells within a column in excel VBA where we want to apply a formula. Let us see how we can do this.

Steps:

  • First, go to the Developer tab and select Visual Basic.

opening VBA to apply formula to entire column excel

  • Now, select Insert in the VBA window and click on Module.

inserting VBA module to apply formula to entire column excel

  • Next, type in the formula below in the new window:
Public Sub Range_Formula()
Range("C5:C10").Formula = "=B5*0.03"
End Sub

VBA code to apply formula to entire column excel

  • Then, open the macro from the Developer tab by clicking on Macro.
  • Now, in the Macro window, select the Range Formula Macro and click Run.

  • Immediately, this will insert the formula into all the cells of the range that we specified.

Read More: How to Apply Formula to Entire Column Without Dragging in Excel


2. Applying AutoFill in VBA

AutoFill is a great command in excel VBA to work with data that are repetitive. We can easily use this by specifying the formula to fill with and the range of cells that we want to fill. Follow the steps below to achieve this.

Steps:

  • To begin with, double-click on cell C5 and insert the following formula, and press Enter.
=B5*0.03

VBA autofill to apply formula to entire column excel

  • Now, open a VBA module as before and insert the following macro code:
Public Sub AutoFill()
Range("C5").AutoFill Range("C5:C10")
End Sub
  • Finally, run this macro and you should get the formula for the entire range of cells.

Read More: How to Create a Formula in Excel without Using a Function (6 Approaches)


Similar Readings


3. Utilizing Range Value in Excel VBA

We can use the Value function in the Range command to apply a formula to an entire column inside excel VBA. For this, type the following code and then do the same steps as before.

Public Sub Range_Value()
Range("C5:C10").Value = "=B5*0.03"
End Sub


Apply Formula to Entire Column Without Dragging in Excel

Although VBA might be the best option when working with large datasets, there is also a very handy tool to quickly fill a column with a certain formula. This can be done with the Fill Handle tool. Let us see how we can do this.

Steps:

  • For this, type the following formula in cell C5:
=B5*0.03
  • Then, double-click on the Fill Handle icon as in the image below and this should do the job.

Apply Formula to Entire Column Without Dragging in Excel


Apply Formula to Entire Column Using Keyboard Shortcut in Excel

If you are someone who likes to work more with the keyboard, then we have a cool shortcut that you can use to apply a formula to an entire column. Follow the steps below.

Steps:

  • To begin with, enter the previous formula in cell C5.
  • In addition, select all the cells from C5 to C10.
  • Finally, press Ctrl+D and this will fill the entire column with the formula.

Read More: How to Create a Formula in Excel for Multiple Cells (9 Methods)


Conclusion

I hope that you were able to apply the methods that I showed in this tutorial on how to apply a formula to an entire column using excel VBA. As you can see, there are quite a few ways to achieve this. So wisely choose the method that suits your situation best. If you get stuck in any of the steps, I recommend going through them a few times to clear up any confusion. Lastly, to learn more excel techniques, follow our ExcelDemy website. If you have any queries, please let me know in the comments.


Related Articles

Nazmul Hossain Shovon

Nazmul Hossain Shovon

Hello, I am Nazmul Hossain. I am currently working full-time in Exceldemy as an Excel & VBA Content Developer. I have completed my bachelors in Naval Architecture and Marine Engineering from Bangladesh University of Engineering and Technology. I am interested in working with MS Excel. I also like coding web applications a lot.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo