4
\$\begingroup\$

A quick description of what I want:

  • Loop through a particular cell row and it's columns
  • If any cell contains 0, then delete the entire column

So let's say 4,4 was 0, then delete that column (D)

My code works but takes a while

Dim lColumn As Long
Dim iCntr As Long
lColumn = 120
For iCntr = lColumn To 4 Step -1
If Cells(4, iCntr) = 0 Then
Columns(iCntr).Delete
End If
Next

Surely there is a faster way to do this.

\$\endgroup\$
1
\$\begingroup\$

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
        Else
        Set oUnion = Union(oCell, oUnion)
        End If
    End If
Next
If Not oUnion Is Nothing Then
    oUnion.EntireColumn.Delete
End If
Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic

Why is this faster?

  1. 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.

  1. 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.

\$\endgroup\$
  • \$\begingroup\$ how did you work out it was 33 times faster? (silly question i know) \$\endgroup\$ – Robot_enthusiast Mar 6 '17 at 16:09
  • \$\begingroup\$ I used the windows API Timer \$\endgroup\$ – jkpieterse Mar 6 '17 at 16:09
  • \$\begingroup\$ Private Declare PtrSafe Function getFrequency Lib "kernel32" Alias "QueryPerformanceFrequency" (cyFrequency As Currency) As Long Private Declare PtrSafe Function getTickCount Lib "kernel32" Alias "QueryPerformanceCounter" (cyTickCount As Currency) As Long \$\endgroup\$ – jkpieterse Mar 6 '17 at 16:10
  • \$\begingroup\$ and is that like a thing you often do or should do to test the efficiency of algorithms? \$\endgroup\$ – Robot_enthusiast Mar 6 '17 at 16:11
  • \$\begingroup\$ and also where would you put that? \$\endgroup\$ – Robot_enthusiast Mar 6 '17 at 16:13

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.