How Can I Automatically Delete Only Completely Blank Columns in Excel 2010?

[happydz]

Hello everyone,

I'm using Excel 2010 Professional and I need help with deleting completely blank columns from my worksheet.

Normally, I use the "Go To Special" feature (Rechercher et sélectionner > Sélectionner les cellules > Cellules vides) to select all blank cells, but there’s a problem: this method selects all blank cells, including those in columns that contain a mix of data and blank cells. As a result, it also selects blank cells in columns that have data, which I don’t want to delete.

What I need is a way to automatically select only the columns that are completely blank (i.e., columns where every single cell is empty) and delete them, while leaving columns with mixed data untouched.

Is there any built-in feature or workaround in Excel 2010 that can help with this task without having to manually go through each column or using a script?

Additional Information:

• I’m working with large datasets, so manually checking each column would be very time-consuming.
• It’s important that only the entirely blank columns are deleted, and any column with even a single non-blank cell should remain.

I’ve attached a screenshot of what the issue looks like—some columns contain both data and blank cells, while others are completely empty.

Thanks so much for any advice or suggestions!

 

[Logit]

Option Explicit

Sub DeleteEmptyColumns()
    Dim ws As Worksheet
    Dim col As Long
    Dim lastCol As Long
    
    ' Set the active worksheet
    Set ws = ActiveSheet
    
    ' Find the last used column in the sheet
    lastCol = ws.Cells(1, ws.Columns.Count).End(xlToLeft).Column
    
    ' Loop through all columns in the used range from right to left
    For col = lastCol To 1 Step -1
        ' Check if the column is empty
        If Application.WorksheetFunction.CountA(ws.Columns(col)) = 0 Then
            ' If the column is empty, delete it
            ws.Columns(col).Delete
        End If
    Next col
End Sub

 

[happydz]

Thank you for your suggestion regarding the VBA code! I appreciate the effort you've put into providing a solution. However, I'm looking for a method that utilizes Excel's built-in features or any tricks, as I prefer to avoid scripts or macros.
If anyone has tips or tricks using only the standard Excel tools to delete completely blank columns, I would be very grateful for your insights. Thanks again!

 

[Logit]

Delete Blank Columns in Excel (3 Easy Ways + VBA)

Scroll down about 2/3 of the page and look for : Delete Blank Columns Using Go-To Special

Note the posted warning about having individual blank cells elsewhere in your sheet. If this method is not acceptable
the only alternative is to highlight each blank column and press the DELETE key on your keyboard or right click and
select DELETE.

I understand your reluctance to using a macro but a macro is the fastest and most effective method of deleting blank
columns.