Optimizing/Streamlining Excel VBA for Deleting empty columns

Question

I've been using the code below from jonhaus.hubpages.com to remove the empty columns I have.

    'Delete empty columns
Dim c As Integer
c = ActiveSheet.Cells.SpecialCells(xlLastCell).Column
    Do Until c = 0
    If WorksheetFunction.CountA(Columns(c)) = 0 Then
            Columns(c).Delete
    End If
    c = c - 1
Loop

However, as I've been writing the VBA, it's gotten kinda bloated and slow... I'm trying to optimize and streamline my code by eliminating loops, copy/pastes, etc.

Do y'all have suggestions for code that would do the same thing (deleting entire emtpy columns) WITHOUT requiring looping "Do Until/If/End If/Loop" statements?

References: http://jonhaus.hubpages.com/hub/Excel-VBA-Delete-Blank-Blank-Columns http://www.ozgrid.com/VBA/SpeedingUpVBACode.htm

Just a thought but rather doing the iterative .Delete inside the loop, you could create a list of columns to be deleted, and then delete them all in one .Delete statement, outside the loop. The other obvious improvements are to disable Application.ScreenUpdating and set Application.Calculation = xlManual during run-time. (remember to restore their normal functionalities at the end of subroutine. — David Zemens

David Zemens David Zemens · Accepted Answer · 2014-09-22T17:58:18

Expanding on my comment above, create the range inside the loop, but delete it only once.

Dim c As Integer
Dim rngDelete As Range

c = ActiveSheet.Cells.SpecialCells(xlLastCell).Column
    Do Until c = 0
        If WorksheetFunction.CountA(Columns(c)) = 0 Then
            'Combine each empty column:
            If Not rngDelete Is Nothing Then
                Set rngDelete = Application.Union(rngDelete, Columns(c))
            Else
                Set rngDelete = Columns(c)
            End If
        End If
        c = c - 1
    Loop

    'Deletes ALL empty columns at once:
    rngDelete.EntireColumn.Delete

The other obvious improvements are to disable Application.ScreenUpdating and set Application.Calculation = xlManual during run-time. (remember to restore their normal functionalities at the end of subroutine.

Optimizing/Streamlining Excel VBA for Deleting empty columns

1 Answers