VBA speed up column deletion code

LNG2013

Active Member
Joined
May 23, 2011
Messages
466
Hello

I have the below code. But it seems to be slowing down the rest of my code taking about 1 min to complete.

I have it as 1 of 8 subs that get called in order.
Prior to all the calls I also have:

Application.ScreenUpdating = False
Application.DisplayAlerts = False
Application.EnableEvents = False

I turn them all on after the code completes.

VBA Code:
Sub FDeleteBlankColumns()


Dim iCntr
Dim Rng As Range
Set Rng = Range("B2:M500")

    For iCntr = Rng.Column + Rng.Columns.Count - 1 To Rng.Column Step -1
        If Application.WorksheetFunction.CountA(Columns(iCntr)) = 0 Then Columns(iCntr).EntireColumn.Delete
    Next

End Sub


Thank you in advance!
 

Excel Facts

Can you sort left to right?
To sort left-to-right, use the Sort dialog box. Click Options. Choose "Sort left to right"
I obviously don't have your workbook to test with, but your code works nearly instantaneously on a test workbook I threw together. Could your workbook be trying to calculate while the code is running, so perhaps setting calc to manual during the execution might help?
 
Upvote 0

Forum statistics

Threads
1,223,248
Messages
6,171,027
Members
452,374
Latest member
keccles

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top