This version is about 33 times faster:
Dim oUnion As Range
Dim oCell As Range
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
For Each oCell In Range("D4:DS4")
If oCell.Value = 0 Then
If oUnion Is Nothing Then
Set oUnion = oCell
Set oUnion = Union(oCell, oUnion)
If Not oUnion Is Nothing Then
Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic
Why is this faster?
- Minimize the number of interactions with Excel.
This procedure first gathers a set of all cells that comply with the condition (=0). Then it deletes those cells (columns) in one command, rather than deleting them one at the time. The routine could be made even faster by first reading all values into an array and testing the values of the array in the loop.
- For Each loops are faster when you are traversing objects
If you are running through all objects in a collection, a for each loop is generally faster than a counted loop.
Time, time and time again.
If you are optimising code for performance never just assume. Time your code. And time it multiple times too, as other programs (Windows!) might affect your results. It is a good idea to time variants A and B of your routine multiple times in random order. Suspect the result? Time again.