We recently showed you how to hide columns (and rows), which is helpful for making spreadsheets more readable. However, sometimes you may want to completely delete a column instead of just hiding it. In this post, we’ll teach you how to remove columns entirely.

You may find yourself wanting to delete entire columns if they contain outdated data or bad formulas. Deleting columns can also be used to move objects, such as charts, to the left as columns disappear. This may be applicable if you create a chart programmatically far off to the right of the data then remove all of the data. Instead of repositioning the chart directly, you can bring it closer to left side. Note that if columns containing a chart are deleted, the chart will be reduced in width equal to the deleted column, which can delete the chart entirely if a single column underlays the entire chart.

Clear vs. Delete

The first question to ask before using the .Delete method on a column (or any range, really), is whether .Clear might be more suitable. Deleting columns is more likely to lead to errors in formulas and cause unintended consequences as objects and data shift left across the sheet.

The VBA Clear method can be applied to data, formulas, or formatting individually, whereas deleting simply removes all traces of that column, its data, its formatting, and any references to it. Moreover, clearing source data will leave the scaling of a chart intact, while deleting a source column destroys the underlying series for the chart and can lead to undesired results.

In many instances, .Clear will do what you need and it’s less likely to wreak havoc on your sheet. However, .Delete has legitimate uses and there are certainly a few situations out there that favor deleting a column over clearing it. One such case may be removing all intermediate columns before delivering the final results to a client.

Deleting a Single Column

It’s important to recognize the difference between a single Column object and a collection of Column objects. The singular term Column refers to a single column, while the plural Columns refers to the set of all columns on any particular sheet. To reference any column, you must specify its index in the collection. Logically, the index in the collection equals the placement on the page, so Column C, the third column, will have an index of 3.

Thus, to delete a single column, say Column Z, the 26th column, simply reference it in the collection and use the .Delete method:

Columns(26).Delete

Fortunately, you don’t have to convert column letters to numbers each time you want to delete a column. It’s also possible to reference Column 26 by its associated letter:

Columns("Z").Delete

Make powerful macros with our free VBA Developer Kit

It’s easy to copy and paste a macro like this, but it’s harder make one on your own. To help you make macros like this, we built a free VBA Developer Kit and wrote the Big Book of Excel VBA Macros full of hundreds of pre-built macros to help you master file I/O, arrays, strings and more - grab your free copy below.

I'll take a free VBA Developer Kit

Deleting Multiple Columns

Since referencing an index in the Columns collection specifies a single Column object, we cannot use single indices to reference more than a single column. Separating indices by a comma will throw an error:

Error Message for Comma Separated Columns
Error message when attempting to separate columns with commas

There are two ways to reference more than a single column for deletion: one is to use letter-reference style and the other is to create an overarching range object.

Letter-reference Style

This is the quickest method if you know exactly which columns to delete, the columns are adjacent, and they do not need to change programmatically. It’s not as easy to change a letter-referenced column using VBA as it is to change a numerically-referenced column. If you have a certain range that will always need to be deleted, though, it’s quite simple to hardcode the columns letter-reference style:

Columns("B:E").Delete

Build a Range

If you need to programmatically determine the columns to delete, it’s not that useful to hardcode the columns in letter-reference style. In that case, it’s better to use number-reference style. However, you cannot simply list the column numbers in the Columns collection call, as that produces the error above. Instead, you must build a range and call .Delete on that range.

To build the range, just wrap the Column objects in a Range line:

Range(Columns(firstColumn), Columns(lastColumn)).Delete

where firstColumn and lastColumn are integers to be set before this line is processed.

Range() will always envelope an entire region, so if you need to delete Columns 1 through 3 and Columns 6 through 8, you’ll need two Range objects, one for the first set and one for the second set. You can combine the ranges using VBA Union so your code can still be written with one line, like this:

Sub DeleteNonAdjacentColumns()
  Union(Range("2:4"), Range("6:7")).Delete
End Sub

or

Sub DeleteNonAdjacentColumns2()
  Union(Range("B:D"), Range("F:G")).Delete
End Sub

The Union command is the way to delete non-adjacent columns using VBA.

The Optional Shift Parameter

There is an optional parameter for the .Delete method: [Shift]. When a specific cell is deleted, the surrounding cells must either shift upwards to fill in the space or left to fill in the space. For example, if the cell B3 is deleted, either the cells in Column B should move up or the cells in Row 3 should move left. In this case, the programmer can choose which occurs with xlShiftUp and xlShiftToLeft.

For Columns().Delete, however, this is irrelevant. Since the entire column is deleted, the only option for Excel is to shift the other columns beyond it to the left. Thus, when you delete a Column (or a Row), there is no need to assign a value for Shift.

What Happens to References

Deleting a column removes any trace of the original, which is replaced with columns from the left. When you move cells containing formulas around the page through the GUI, cell references are re-calculated to remain accurate with the new arrangement. However, with .Delete, this doesn’t happen. If a formula references a column that is subsequently deleted, the formula breaks and throws a #REF error, as its original reference cell no longer exists.

For this reason, it is always prudent to consider using .Clear before jumping straight to .Delete, as the latter can easily break formulas. For complex models, this can destroy the entire workbook as the #REF error cascades through it! Even worse, this #REF error cannot be reversed by inserting new columns for the deleted ones, and the Undo command does not apply to VBA-initiated actions. Once a #REF error is thrown, it can be difficult to fix, especially if the deleted column is weaved into formulas throughout the workbook.

This reference problem also occurs with charts. If the columns connected to a chart are deleted, there’s no way to repopulate the chart because the original columns are now nonexistent and the original series cannot be recovered.

The bottom line is you should proceed with caution when using .Delete. You don’t want to destroy a 10-sheet workbook and have to go through the file recovery process with your fingers crossed.

To learn more VBA tips like this one, subscribe using the form below.