VBA & Macro Problems

How to Delete Columns Except Specific Headers Using VBA in Excel

Guest Writer Oct 10, 2026 868 views

Question details

Need a VBA macro to delete all columns in a worksheet or workbook except those matching specific header names (e.g., Doctor, Visit, TV).

How to Delete Columns Except Specific Headers Using VBA in Excel

Product: Microsoft Excel
Device & OS: not provided
Scenario: Cleaning up large datasets by removing unnecessary data columns and retaining only the required specific columns based on their header names.
Observed behavior: Manually deleting unwanted columns across large worksheets or multiple tabs is extremely time-consuming and prone to human error.

Before you start

Before running the macro, ensure that your column headers are located precisely in Row 1 and unmerge any merged header cells, as merged cells will cause the VBA column deletion loop to fail or delete incorrect data.

Solution 1Recommended

Use VBA Macro to Delete Unwanted Columns in a Single Worksheet

Apply a VBA script to evaluate the first row and automatically delete any column that doesn't match your required header names.

When writing a macro to delete columns or rows, it is crucial to loop backwards (from the last column to the first). If you loop forward, deleting a column shifts the remaining columns left, causing the macro to skip evaluations and leave unwanted columns behind.

Open the VBA Editor

Press Alt + F11 on your keyboard to launch the Microsoft Visual Basic for Applications window.

Insert a New Module

Click on 'Insert' in the top menu bar, then select 'Module' to create a blank workspace for your script.

Write the Backwards Loop Macro

Paste a VBA script that defines your last column (e.g., LastCol = Cells(1, Columns.Count).End(xlToLeft).Column), and uses a 'For i = LastCol To 1 Step -1' loop.

Define the Header Criteria

Inside the loop, use an If statement or Select Case to check if Cells(1, i).Value matches 'Doctor', 'Visit', or 'TV'. If it does not match, execute the command 'Columns(i).Delete'.

Run the Macro

Close the VBA editor, return to your Excel worksheet, press Alt + F8 to open the Macro dialog box, select your new macro, and click 'Run'.

Use VBA Macro to Delete Unwanted Columns in a Single Worksheet

Case Sensitivity Precaution: To prevent the macro from missing headers due to capitalization differences, wrap the cell value evaluation in the UCase() function and compare it against uppercase text.

Solution 2

Loop Through All Worksheets to Keep Specific Columns

Expand the macro to apply the column deletion logic across every single worksheet within your active Excel workbook.

Efficient Data Cleanup in WPS Spreadsheet

Use Macros to Automate Data Cleanup with WPS Spreadsheet

WPS Office provides robust and native support for VBA and macros, allowing you to run the exact same scripts to delete columns by headers. Enjoy advanced data processing tools in a lightweight, user-friendly suite.

1. Open Your Workbook: Launch WPS Spreadsheet and open the file containing the dataset you need to clean.
2. Access the VBA Environment: Navigate to the 'Developer' tab on the ribbon and click on 'Macros', or use the Alt + F11 shortcut to open the VBA Editor.
3. Insert and Customize the Script: Insert a new module and paste your backwards-looping macro designed to keep only specific headers like Doctor, Visit, and TV.
4. Execute the Cleanup: Press F5 or use the 'Run' button in the editor to execute the macro, instantly removing the unwanted columns.

Fully supports standard Excel VBA macros and module scriptingSeamless compatibility with Microsoft Office .xls, .xlsx, and .xlsm formatsLightweight application that runs smoothly on low-spec devicesFree alternative with a highly familiar ribbon user interface

microsoft office alternative - wps office

Frequently Asked Questions

Can I undo a VBA macro if it deletes the wrong columns?

No, actions performed by a VBA macro cannot be undone using the standard Undo button (Ctrl+Z). You should always save a backup copy of your workbook before running any macro that deletes data.

Why is my macro deleting columns that contain the correct headers?

This usually happens due to leading or trailing spaces in your Excel headers, or due to case sensitivity. Use the 'Trim' function to remove invisible spaces and 'UCase' function to standardize the text case in your VBA script before evaluating the header.

Does this macro work on hidden columns?

Yes, standard VBA loops will evaluate and delete hidden columns if their headers do not match your specified list. If you want to skip hidden columns, add an If statement checking the 'Hidden' property (e.g., If Columns(i).Hidden = False).

How do I modify the VBA code to keep different header names?

You can update the 'If' statement or the 'Select Case' conditions in your VBA code to include your specific column names. Simply replace 'Doctor', 'Visit', or 'TV' with your new header names, ensuring the text enclosed in quotation marks matches exactly.

Excel & Data Problems