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, Commented Sep 22, 2014 at 17:46

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

1

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.

answered Sep 22, 2014 at 17:58

David Zemens

53.8k12 gold badges86 silver badges132 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Optimizing/Streamlining Excel VBA for Deleting empty columns

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related