Skip to content

Dear Internet Explorer user: Your browser is no longer supported

Please switch to a modern browser such as Microsoft Edge, Mozilla Firefox or Google Chrome to view this website's content.

Automatically add columns and formulae then fill-down in Excel

Some simple VBA code to add columns in Excel, insert specific formulae at the top then fill-down to the bottom of the sheet.

For those who work with log files, it may be necessary to routinely insert columns at a specific position, insert a formula and then fill-down to derive certain values. This can be automated in Excel using Visual Basic using four simple scripts.

Inserting Columns

To insert columns at a specific position in a worksheet, use the following VBA script.

Sub AddColumns()
'Inserts Two Columns at B and C
Worksheets(1).Range("B:C").EntireColumn.Insert
End Sub

In this example, two columns are inserted where Column B is located. Adjust the range as required.

Adding headers

If the new columns require headers in Row 1, then the following script can do that.

For instance, let’s say that we want the header in B1 to be called “Value 1” and the header in C1 to be called “Value 2”. The following script will do this:

Sub AddHeader()
    Worksheets(1).Range("B1").Formula = "Value 1"
    Worksheets(1).Range("C1").Formula = "Value 2"
End Sub

Adding formulae

After adding columns, you may want to insert a formula to perform a calculation based on data elsewhere in the sheet. Since cells B1 and C1 contain headers, we’ll add them to cells B2 and C2:

Sub AddFormula()
'Inserts specific formulae to cells B2 and C2
    Dim Formulas(1 To 2) As Variant
    With ThisWorkbook.Worksheets("Transposed Data")
        Formulas(1) = "=SUM(E2+G2)"
        Formulas(2) = "=SUM($E$2+2)"
        .Range("B2:C2").Formula = Formulas
'Changes number format in Columns B and C to general
        .Range("B:C").NumberFormat = "General"
    End With
End Sub

This code adds the formula =SUM(E2+G2) to B2 and =SUM($E$2+2) to C2. These should be changed to suit your circumstances. As many formulae can be added to the code as desired, so long as Dim Formulas(1 To 2) As Variant, Formulas( ) and .Range is expanded to accommodate them.

In addition, this code will change the NumberFormat to “General”. Again, this can be changed to whatever type is required. The number type really only becomes an issue of the contents of the cells on the left of the new columns have a different number format. For instance, if Column A had a series of dates and was therefore in Date format, Columns B and C would have been created in Date format too.

Filling-down

Once the automated formulae have been added to the top of the column, you may want them to automatically fill down.

The following code will identify how many rows contain data in the worksheet and then fill-down from B2 to the last data-containing row in Column B.

Sub FillColumn()
    'Fills column to last row of data from Cell B2
    Dim LastRow As Long
    LastRow = Cells.Find(What:="*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    Range("B2:B" & LastRow).FillDown
End Sub

The range can be adjusted to accommodate more columns. In our previous example, we added formulae to the first rows in Columns B and C, so we need to change the code from Range("B2:B" & LastRow).FillDown to Range("B2:C" & LastRow).FillDown.

Conclusion

By using this series of scripts, your worksheet should now automatically:

  1. Insert columns
  2. Add column headers
  3. Add formulae to the first row
  4. Fill down to the last row

Don’t forget that the easiest way to manage this is to link all of the scripts together:

Sub DoEverything()
    AddColumns
    AddHeader
    AddFormula
    FillColumn
End Sub
   

Comments

5 responses to “Automatically add columns and formulae then fill-down in Excel”

On 27 February 2017, sivakumar wrote: Hyperlink chain icon

very good

Reply

On 4 October 2018, Matthew Courchaine wrote: Hyperlink chain icon

Is it possible to modify this to insert a column after the last column containing data, then add a formula?

Reply

On 28 April 2019, sepeverus wrote: Hyperlink chain icon

Many thanks for this simple yet efficient code !

Reply

On 13 November 2019, Joanne wrote: Hyperlink chain icon

Hi, May i ask that how to automatically fill all the above for formula without adding header(s) and actually i want all my files have additional column and formula as well?

Reply

On 29 April 2020, Swap wrote: Hyperlink chain icon

Can you pls write the code to sum below Coloumn ‘B’ value with respect to coloumn ‘A’ or Coloumn ‘C’.

Name Amount site Purpose Date
qwe 123 sss opi 01-01-2015
qwe 345 sss qas 01-01-2015
asd 345 kkk zxc 02-02-2015
zxc 789 sss mnb 03-03-2015
& so on…….

Reply

Have Your Say

The following HTML is permitted:
<a href="" title=""> <b> <blockquote cite=""> <code> <em> <i> <q cite=""> <strike> <strong>

Comments will be published subject to the Editorial Policy.