VBA Delete Column in Excel — Remove Columns Without #REF! Errors or Skipped Columns

TL;DR — Columns("C").Delete is Insert run backwards: it removes column C and drags everything to its right one column left. Same shift, opposite direction — with a destructive twist. Any formula that referenced a deleted cell has nothing left to point at, so it collapses to #REF! permanently. And deleting inside a forward loop skips columns, because every delete renumbers the ones after it. Delete right-to-left, or collect the targets into a Union and delete once.

Sub DeleteFlaggedColumns()
    Dim ws As Worksheet, c As Long, kill As Range
    Set ws = ThisWorkbook.Worksheets("Report")
    For c = ws.UsedRange.Columns.Count To 1 Step -1     ' scan, do NOT delete mid-loop
        If ws.Cells(1, c).Value = "" Then
            If kill Is Nothing Then Set kill = ws.Columns(c) _
            Else Set kill = Union(kill, ws.Columns(c))
        End If
    Next c
    If Not kill Is Nothing Then kill.Delete             ' one shift, nothing renumbers
End Sub

Deleting a column uses the exact same grid-shift as inserting one — it just pulls left instead of pushing right. What makes Delete the sharper tool is that the shift is destructive: data leaves, and references that aimed at it break. Two failure modes account for almost every delete-column bug — broken references and skipped columns — and both come straight from the shift.

What you'll learn

The mental model — Delete pulls the grid left, and references into the gap die
Why you get #REF!, and which formulas survive the shift versus which shatter
The forward-loop trap — why a delete loop skips columns, and the right-to-left fix
Deleting by condition with a single Union so nothing renumbers mid-loop
When you wanted ClearContents, not Delete

The mental model: Delete pulls left, and the gap takes references with it

Columns("C").Delete removes column C, then slides D into C's place, E into D's, and so on. Excel adjusts the references that survive — a formula reading =SUM(D2:D100) becomes =SUM(C2:C100) because that data moved left and Excel followed it. That part is friendly.

The unfriendly part is references that pointed into column C itself. When C is gone, a formula like =C5*A5 cannot shift onto nothing — there is no cell to land on — so it becomes =#REF!*A5 and stays broken. #REF! is not a temporary glitch; it is Excel telling you the reference was destroyed. And it can strike on a sheet you are not even looking at, because a formula three tabs over may quietly depend on the column you just deleted.

The rule that follows: before deleting a column other formulas read from, decide what happens to those formulas — repoint them, or convert the dependents to values first. Delete the column blindly and you may find #REF! scattered across the workbook after the macro "succeeds."

The forward-loop trap: the number-one delete bug

You want to delete columns C, E, and G. The instinct is a forward loop:

Dim c As Long
For c = 3 To 7 Step 2
    ws.Columns(c).Delete          ' WRONG - deletes the wrong columns
Next c

Watch what happens. Deleting column 3 (C) pulls everything left, so the old column E is now column D, and old G is now F. The loop then deletes column 5 — which is no longer E — and by the third pass it is deleting whatever drifted into column 7. You end up removing the wrong columns and leaving the ones you meant to drop. The delete renumbers the columns you have not reached yet.

There are two correct fixes, and the second is better:

' Fix 1: loop RIGHT-TO-LEFT so earlier deletes never move later targets
For c = 7 To 3 Step -2
    ws.Columns(c).Delete
Next c

' Fix 2 (preferred): collect into a Union, delete ONCE - order stops mattering
Dim kill As Range
Set kill = Union(ws.Columns(3), ws.Columns(5), ws.Columns(7))
kill.Delete

This is the same disease you already treat when deleting rows — you loop bottom-up there for exactly this reason. Here the axis is horizontal, so it is right-to-left. The Union approach sidesteps direction entirely and is dramatically faster: one delete triggers one shift and one recalc, whereas N separate deletes trigger N of each.

Deleting by condition: collect, then delete once

The Union pattern shines when you delete columns by a rule — every column with a blank header, every column whose title matches a list, every empty column. The discipline is: scan and collect, never delete inside the scan.

Sub DeleteEmptyColumns()
    Dim ws As Worksheet, c As Long, kill As Range
    Set ws = ThisWorkbook.Worksheets("Data")
    For c = 1 To ws.UsedRange.Columns.Count
        If Application.WorksheetFunction.CountA(ws.Columns(c)) = 0 Then
            If kill Is Nothing Then Set kill = ws.Columns(c) _
            Else Set kill = Union(kill, ws.Columns(c))
        End If
    Next c
    If Not kill Is Nothing Then kill.Delete
End Sub

Because the deletion happens after the loop, the scan can run in any direction — nothing renumbers while you are still deciding. Test Not kill Is Nothing before calling .Delete, or an empty match set throws run-time error 91 ("Object variable not set").

Delete versus ClearContents: know which you meant

The most common accidental-#REF! source is reaching for Delete when you only wanted the data gone:

Columns("C").Delete removes the column structure and shifts everything left. References into it break; the sheet's column layout changes.
Columns("C").ClearContents empties the cells and shifts nothing. Formulas that read column C now see blanks (or zeros), the layout is untouched, and no reference breaks.

If a downstream formula, a chart, a ListObject table, or a named range depends on that column existing, you want ClearContents. Reserve Delete for when the column genuinely should not be there. And when a column belongs to a table, delete it through the table — ws.ListObjects("Sales").ListColumns("Tax").Delete — rather than Columns(...).Delete, which fights the table's own structure.

How ExcelMaster helps

Deleting a column looks trivial and hides three traps — references that break into #REF! on sheets you are not watching, a loop that renumbers itself and deletes the wrong columns, and the Delete-versus-Clear choice that decides whether your layout survives.

ExcelMaster lets you say what you want — "delete every column with no header, keep the totals intact" — and it collects the matches into a single Union, deletes them in one shift so nothing renumbers, warns when a deletion would break a formula elsewhere, and uses ClearContents when you meant "empty" rather than "remove." You keep the workbook and the code.

Frequently asked questions

How do I delete a column in VBA?

Use Columns("C").Delete to remove column C; every column to its right shifts one step left. Qualify it — ThisWorkbook.Worksheets("Report").Columns("C").Delete — so it does not act on whatever sheet is active. Columns("C:E").Delete removes three adjacent columns in one call.

Why do I get #REF! after deleting a column?

Because a formula referenced a cell in the deleted column, and once that column is gone the reference has nothing to point at, so it becomes #REF! permanently. Formulas that referenced columns to the right of the deletion are fine — Excel shifts them left with the data. Only references into the deleted range break. Check other sheets too; the broken formula may not be where you deleted.

Why does my delete loop skip columns?

Because deleting a column renumbers every column after it, so a forward For c = 1 To n loop moves past columns that shifted into a slot you already passed. Loop right-to-left (For c = n To 1 Step -1), or — better — collect the target columns into a Union and call .Delete once, which removes them in a single shift regardless of order.

How do I delete multiple columns at once?

For adjacent columns, Columns("C:E").Delete. For scattered columns, build a Union — Union(Columns(3), Columns(5), Columns(7)).Delete — so all of them go in one shift. This is both correct (no renumbering) and much faster than deleting them one at a time.

What is the difference between Delete and ClearContents for a column?

Delete removes the column and shifts the rest left, which can break references and change your layout. ClearContents empties the cells and shifts nothing, leaving the structure and every reference intact. If anything downstream depends on the column existing, use ClearContents.

Tested in

Tested in: Excel 365 (Windows 11), VBA 7.1 — last verified 2026-09-15.