Macro to Insert and Delete Columns in Excel

These VBA macros insert columns, delete the selected columns, or delete a column by its header name.

Works inExcel 2010 and later, Windows and Mac
UndoNot possible. Save a copy first
Save asMacro-Enabled Workbook (.xlsm)

New to macros? See how to use the macros on this site: copy the code, paste it into a module with Alt + F11, and run it with Alt + F8.

Insert columns to the left of the active cell

Sub InsertColumns()
    Dim n As Long
    n = Application.InputBox("How many columns to insert?", Type:=1)
    If n < 1 Then Exit Sub
    ActiveCell.EntireColumn.Resize(, n).Insert
End Sub

Delete the selected columns

Sub DeleteSelectedColumns()
    Selection.EntireColumn.Delete
End Sub

Delete a column by its header

Type the header, such as “Notes”, and the column with that header in row 1 is deleted, wherever it is:

Sub DeleteColumnByHeader()
    Dim h As String, c As Range
    h = InputBox("Header of the column to delete:")
    If h = "" Then Exit Sub
    Set c = Rows(1).Find(What:=h, LookAt:=xlWhole, MatchCase:=False)
    If c Is Nothing Then MsgBox "Not found: " & h Else c.EntireColumn.Delete
End Sub

Handy for reports where columns move around. LookAt:=xlWhole matches the full header only, so “Note” won’t delete “Notes”.

Things to know

  • Formulas that referred to a deleted column show #REF!.
  • There’s no Undo. Save a copy first.
  • Keyboard alternative: Ctrl + Space selects the column, then Ctrl + - deletes it.

Related macros