How to delete entire columns based on header value in Excel?
In daily Excel work, it is common to encounter situations where you need to quickly remove entire columns according to the values in the header row. For instance, you might have a sheet containing several years of product data in columns, and you want to delete all columns labeled “old” to clean up outdated information. Manually locating and deleting these columns can become tedious and error-prone, especially in large datasets. This tutorial introduces two practical and effective methods to efficiently delete entire columns based on header values in Excel, helping you streamline data management and avoid unnecessary manual work.
Delete entire columns based on header value with VBA code
Delete entire columns based on header value with Kutools for Excel
Delete entire columns based on header value with VBA code
For users familiar with macros, using VBA is a flexible way to automatically delete columns based on specific header values. This approach is particularly useful when dealing with large data tables, or if you need to frequently perform the same column removal task on different datasets. Below are the detailed steps for implementing this method:
1. Press Alt + F11 simultaneously to open the Microsoft Visual Basic for Applications (VBA) editor. This allows you to input and run custom code within Excel.
2. In the VBA editor window, from the menu bar, click Insert > Module. This will insert a new module into your project, in which you can paste the code.
VBA code: delete entire columns based on header value
Sub DeleteSpecifcColumn()
Set MR = Range("A1:D1")
For Each cell In MR
If cell.Value = "old" Then cell.EntireColumn.Delete
Next
End SubNotes:
3. After pasting the code and making necessary adjustments, press F5 to run it. Excel will automatically delete entire columns where the header matches the specified value. You do not need to make a selection beforehand, as the code works based on the range defined in the script.
If an error is encountered (for example, if the header value is not found or the specified range is incorrect), double-check both the header text and the cell range. Also, remember to save your workbook before running macros, as deleted columns cannot be easily restored without undoing.
This VBA approach is ideal for automating repetitive cleanup tasks, especially in templates or data imports where columns with certain headers consistently need to be removed. However, if you are not comfortable with code or need an instant, interactive option, the Kutools for Excel method below may be more suitable.
Delete entire columns based on header value with Kutools for Excel
If you prefer using graphical interfaces over code, Kutools for Excel provides an intuitive way to select and delete columns based on header values. This method is highly accessible for all types of users, especially those who frequently perform data filtering and restructuring. Kutools’ Select Specific Cells utility makes the process straightforward:
1. First, highlight the range containing the columns you may want to delete. Make sure to include the header row in your selection. Then, navigate to Kutools > Select > Select Specific Cells. This feature will help you quickly locate columns matching your criteria. See screenshot:
2. In the Select Specific Cells dialog box, select the Entire column option under the Selection type section. In the Specific type drop-down, choose Equals, then enter your desired header value (for example, “old”) into the box. After setting your criteria, click OK. See screenshot:
The Equals setting ensures only columns whose header value matches exactly will be selected. Be careful with spelling and capitalization to match your headers precisely. If you want to select columns that contain (rather than exactly match) a keyword, you can choose the Contains type instead for a broader search.
3. After confirming, a dialog box will notify you how many columns were found and selected according to your criteria. Click OK to close it, and the relevant columns will be highlighted.
4. To delete the selected columns, right-click one of the selected column headers, and choose Delete from the context menu. All matching columns will be removed at once. This method ensures accuracy and minimizes the risk of deleting unintended data.
If you want to have a free trial (30-day) of this utility, please click to download it, and then go to apply the operation according above steps.
It is worth noting that the Kutools method is especially helpful when working with complex tables or unfamiliar data layouts, as the preview and interactive selection steps reduce potential mistakes. However, Kutools is an add-in and requires installation—if you do not have it, you may need to use manual selection or try the formula-based alternative below.
Best Office Productivity Tools
Supercharge Your Excel Skills with Kutools for Excel, and Experience Efficiency Like Never Before. Kutools for Excel Offers Over 300 Advanced Features to Boost Productivity and Save Time. Click Here to Get The Feature You Need The Most...
Office Tab Brings Tabbed interface to Office, and Make Your Work Much Easier
- Enable tabbed editing and reading in Word, Excel, PowerPoint, Publisher, Access, Visio and Project.
- Open and create multiple documents in new tabs of the same window, rather than in new windows.
- Increases your productivity by 50%, and reduces hundreds of mouse clicks for you every day!
All Kutools add-ins. One installer
Kutools for Office suite bundles add-ins for Excel, Word, Outlook & PowerPoint plus Office Tab Pro, which is ideal for teams working across Office apps.
- All-in-one suite — Excel, Word, Outlook & PowerPoint add-ins + Office Tab Pro
- One installer, one license — set up in minutes (MSI-ready)
- Works better together — streamlined productivity across Office apps
- 30-day full-featured trial — no registration, no credit card
- Best value — save vs buying individual add-in