Delete Blank or Empty Columns Use Excel VBA

Blank columns can often cause problems with formulas. If you find that you are manually searching out and deleting blank columns in your data sets, this simple macro can help automate delete blank columns. If you want to delete blank rows, click here.

VBA Code

Sub DeleteBlankColumns()
'Step1:  Declare your variables.
    Dim MyRange As Range
    Dim iCounter As Long
'Step 2:  Define the target Range.
    Set MyRange = ActiveSheet.UsedRange
    
'Step 3:  Start reverse looping through the range.
    For iCounter = MyRange.Columns.Count To 1 Step -1
    
'Step 4: If entire column is empty then delete it.
       If Application.CountA(Columns(iCounter).EntireColumn) = 0 Then
       Columns(iCounter).Delete
       End If
'Step 5: Increment the counter down
    Next iCounter
End Sub

How This Macro Works

In this macro, we are using the UsedRange property of the ActiveSheet object to define the range we are working with. The UsedRange property gives us a range that encompasses the cells that have been used to enter data. We then establish a counter that starts at the last column of the used range, checking if the entire column is empty. If the entire column is indeed empty, we remove the column. We keep doing that same delete for every loop, each time incrementing the counter to the previous column.

  1. Step 1 first declares two variables. The first variable is an object variable called MyRange. This is an Object variable that defines the target range. The other variable is a Long Integer variable called iCounter. This variable serves as an incremental counter.
  2. Step 2 fills the MyRange variable with the UsedRange property of the ActiveSheet object. The UsedRange property gives us a range that encompasses the cells that have been used to enter data. Note that if we wanted to specify an actual range or a named range, we could simply enter its name: Range("MyNamedRange").
  3. In this step, the macro sets the parameters for our incremental counter to start at the max count for the range (MyRange.Columns.Count) and end at 1 (the first row of the chosen range). Note that we are using the Step-1 qualifier. Because we specify Step -1, Excel knows we are going to increment the counter backwards; moving back one increment on each iteration. In all, Step 3 tells Excel that we want to start at the last column of the chosen range, moving backward until we get to the first column of the range.
  4. When working with a range, you can explicitly call out a specific column in the range by passing a column index number to the Columns collection of the range. For instance, Range("A1:E11").Columns(2) points to the second column in the range (column B). In Step 4, the macro uses the iCounter variable as an index number for the Columns collection of MyRange. This helps pinpoint exactly which column we are working with in the current loop. The macro checks to see whether all the cells in that column are empty. If they are, the macro deletes the entire column.
  5. In Step 5, the macro loops back to increment the counter down.

How to Use This Macro

The best place to store this macro is in your Personal Macro Workbook. This way, the macro is always available to you. The Personal Macro Workbook is loaded whenever you start Excel. In the VBE Project window, it is named personal.xlsb.

  1. Activate the Visual Basic Editor by pressing ALT F11.
  2. Right-click personal.xlsb in the Project window.
  3. Choose InsertModule.
  4. Type or paste the code in the newly created module.
    Insert a Module in Personal Macro Workbook

Don't see personal.xlsb in your project window?

If you don't see personal.xlsb in your project window, it doesn't exist yet. You'll have to record a macro, using Personal Macro Workbook as the destination.

To record the macro in your Personal Macro Workbook, select the Personal Macro Workbook option in the Record Macro dialog box before you start recording. This option is in the Store Macro In drop-down box.

Store Macro In Personal Macro Workbook

Simply record a couple of cell clicks and then stop recording. You can discard the recorded macro and replace it with this one.

Comments

  1. TheAZAndyman says

    Thank you.
    The looping from the right to the left is what I needed. Thanks.
    I kept deleting a column, all columns shift left my loop would skip the next column since now it is the column # that was just deleted then my range was 1 less column and I would encounter a run time error since it tried to do something outside the bounds of the range.
    I also appreciate the explanation. To many code examples out there that provide an answer with no comments or explanation on what they are doing. A lot of them I find out provide an answer by thinking in a box and in the grand scheme of things are providing a poor solution. Like using activesheet or selection instead of working on a range variable.

    • SteveFromSanAntonio says

      Just incase folks don’t already know, deleting rows is much quicker if done from the bottom up for the same reason that AZAndyman relates. (Found that out the “old fashion” way… )

      Great article! Thanks!

  2. Rachael says

    Thanks so much for this! Unfortunately, my cells aren’t blank, but empty strings (“”). Any suggestions on how to deal with those. For instance, can I change by empty strings to null/blank? I do need to maintain the columns that have actual numbers/values in them.

    Thanks!

Leave a Reply

Your email address will not be published. Required fields are marked *