How to Delete Empty Columns with Header in Excel

If you've ever worked with huge datasets or received a spreadsheet with empty columns, you know how time?consuming and irritating manually removing them can be. Fortunately, Excel has a robust set of tools and functions that can assist in automating this process. Whether you're a novice or a seasoned Excel user, this tutorial will teach you how to rapidly clean up your spreadsheets and improve your data analysis workflow.

It is critical to comprehend the relevance of empty columns with headers. Empty columns can clog your data, making it difficult to understand or visualise. By deleting these unnecessary columns, you may streamline your data and concentrate on the important information, saving time and effort.

Delete Empty Columns with Header in Excel

Here, we first create a VBA module and then run it to complete the task. So let us see a simple process to know how you can delete empty columns with headers in Excel.

Step 1

Consider an Excel sheet where you have the data in table format with at least one empty column, similar to the below image.

First, right?click on the sheet name and click View Code to open the VBA application.

Right click > View code.

Step 2

Then click on Insert, select Module, and copy the below code into the text box.

Insert > Module > Copy.

Example

Sub Macro1()
    Dim xEndCol As Long
    Dim I As Long
    Dim xDel As Boolean
    On Error Resume Next
    xEndCol = Cells.Find("*", SearchOrder:=xlByColumns, SearchDirection:=xlPrevious).Column
    If xEndCol = 0 Then
        MsgBox "There is no data on """ & ActiveSheet.Name & """ .", vbExclamation, "Kutools for Excel"
        Exit Sub
    End If
    Application.ScreenUpdating = False
    For I = xEndCol To 1 Step -1
        If Application.WorksheetFunction.CountA(Columns(I)) <= 1 Then
            Columns(I).Delete
            xDel = True
        End If
    Next
    If xDel Then
        MsgBox "All blank and column(s) with only a header row have now been deleted.", vbInformation, "Delete Empty Columns"
    Else
        MsgBox "There are no Columns to delete as each one has more data (rows) than just a header.", vbExclamation, "Delete empty columns"
    End If
    Application.ScreenUpdating = True
End Sub

Step 3

Then click F5 to run the module and click Ok to complete the task.

Conclusion

In this tutorial, we have used a simple example to demonstrate how you can delete empty columns with headers in Excel to highlight a particular set of data.