VBA & Macro Problems

How to Use Excel VBA to Delete Empty Columns with Headers

Huma Ashraf Ch Sep 28, 2026 870 views

Question details

Need a VBA macro to automatically delete worksheet columns that contain a header but have no data underneath.

How to Delete Empty Columns with Headers Using Excel VBA

Product: Excel
Device & OS: not provided
Scenario: Cleaning up a large dataset by removing unused columns without deleting columns that contain actual data, errors, or the header row itself.
Observed behavior: Columns with headers but no data (or specific errors like #N/A) need to be identified and deleted, but standard macro logic often fails to distinguish them or shifts column indexes incorrectly.

Before you start

Before running any VBA macro that permanently deletes columns or rows, please save a backup copy of your workbook, as macro actions cannot be undone using the standard Undo feature.

Solution 1Recommended

Use a VBA Macro to Check and Delete Empty Columns (Right to Left)

Loop through the used columns from right to left, checking if rows below the header are completely empty before deleting the column.

When deleting columns in VBA, always loop backwards (from right to left). If you loop forwards, deleting a column shifts the remaining columns to the left, which changes their index numbers and causes the macro to skip columns. The code should inspect the data range starting from row 2 to ensure headers are preserved.

Open the VBA Editor

Press ALT + F11 on your keyboard to open the Visual Basic for Applications (VBA) Editor in Excel.

Insert a New Module

In the top menu, click on Insert and select Module to create a blank script window.

Input the Deletion Macro

Write a For loop that steps backwards (e.g., For i = LastCol To 1 Step -1). Inside the loop, use Application.WorksheetFunction.CountA(Range(Cells(2, i), Cells(LastRow, i))) to count non-blank cells. If the result is 0, execute Columns(i).Delete.

Run the Macro

Close the VBA Editor. Go to the Developer tab, click on Macros, select your newly created macro, and click Run.

Use a VBA Macro to Check and Delete Empty Columns (Right to Left)

Handle Blank Rows First: Ensure you clean up completely blank rows or stray spaces before running this macro, as invisible characters might prevent the column from being recognized as empty.

Solution 2

Modify the Macro to Delete Columns Containing Error Values

Extend the macro logic to delete columns if they contain specific error values like #N/A or NULL strings.

Run Excel VBA Macros Seamlessly in WPS Spreadsheets

WPS Office is a powerful, lightweight suite that fully supports Excel files and offers an intuitive interface for running and managing macros. Easily clean up your data using VBA in WPS Spreadsheets without compatibility issues.

1. Open Your Workbook: Open your macro-enabled workbook (.xlsm) directly in WPS Spreadsheets.
2. Navigate to Developer Tools: Click on the Developer tab located on the top ribbon menu.
3. Manage Macros: Click on Macros to view and run your existing column-deletion scripts, or select Visual Basic Editor to write or modify new VBA code.

Seamless compatibility with Microsoft Excel formats (.xlsx, .xlsm, .csv).Built-in Developer tools to run, edit, and manage VBA macros easily.Lightweight installation and incredibly fast performance on all datasets.Free to download with a familiar user interface to flatten the learning curve.

Frequently Asked Questions

Why do I need to loop backwards (right to left) when deleting columns in VBA?

If you loop forwards (left to right), deleting a column shifts all subsequent columns to the left, which changes their index numbers. This causes the loop to skip the next column in sequence. Looping backwards ensures the indexes of unchecked columns remain unchanged.

Will this macro accidentally delete my header row?

No. As long as the macro code specifies the checking range starting from row 2 (for example, Cells(2, i) to Cells(LastRow, i)), the header in row 1 is ignored during the empty-check evaluation.

Why isn't the macro deleting columns that look empty to me?

The columns might contain hidden formatting, spaces, or invisible zero-length strings (often caused by formulas). You should manually clear these blank rows or use the Clean function before running the deletion script.

Can I use VBA to delete columns that only contain #N/A?

Yes. You can add a CountIf function to your VBA script to check if the column contains specific errors like "#N/A". If the count of errors matches the total count of filled cells in that column, trigger the delete command.

Excel & Data Problems