Written by Allen Wyatt (last updated March 16, 2019)
This tip applies to Excel 97, 2000, 2002, and 2003
When importing information from an external source, it is possible that the data will contain blank columns—columns with nothing in them. If you import a lot of data, then deleting these columns can be a bother. There are a couple of ways you can approach how to delete these columns.
The first approach works very well if your data is sorted by column. In other words, the data that you import is in ascending order, or you want it in sorted order. In this case, follow these steps:
Figure 1. The Sort Options dialog box.
When sorting in this manner, all the empty columns end up "pushed" to the right, and your data is in a sorted order.
If you don't want your data sorted, then you can use a nifty macro that will check for blank columns in a selected range and then delete those columns. The following macro will do the trick:
Sub DeleteEmptyColumns() first = Selection.Column last = Selection.Columns(Selection.Columns.Count).Column For i = last To first Step -1 If WorksheetFunction.CountBlank(ActiveSheet.Columns(i)) = 65536 Then Columns(i).Delete End If Next i End Sub
To use the macro, select the range of columns in which you want blank columns deleted. The macro steps through the columns and if the column is truly blank, it is deleted. You should note that this macro will delete only columns that are truly empty. If cells within a column include a formula that returns a zero value (and you have the display of zeros values turned off) or that returns an empty string, then the column isn't empty—it contains formulas. In this case, the column won't be deleted.
Note:
ExcelTips is your source for cost-effective Microsoft Excel training. This tip (2660) applies to Microsoft Excel 97, 2000, 2002, and 2003. You can find a version of this tip for the ribbon interface of Excel (Excel 2007 and later) here: Deleting Blank Columns.
Best-Selling VBA Tutorial for Beginners Take your Excel knowledge to the next level. With a little background in VBA programming, you can go well beyond basic spreadsheets and functions. Use macros to reduce errors, save time, and integrate with other Microsoft applications. Fully updated for the latest version of Office 365. Check out Microsoft 365 Excel VBA Programming For Dummies today!
Deleting rows or columns is easy when you use the shortcut described in this tip. Just select the rows or columns and ...
Discover MoreWhen you want to remove information from a worksheet, you can either clear cells or delete cells. This tip examines the ...
Discover MoreNeed to get rid of everything in a worksheet except the formulas? It's easier to make this huge change than you think it is.
Discover MoreFREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."
There are currently no comments for this tip. (Be the first to leave your comment—just use the simple form above!)
Got a version of Excel that uses the menu interface (Excel 97, Excel 2000, Excel 2002, or Excel 2003)? This site is for you! If you use a later version of Excel, visit our ExcelTips site focusing on the ribbon interface.
FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."
Copyright © 2025 Sharon Parq Associates, Inc.
Comments