Columns().Delete Slowest part of Macro

[tqeonline]

I have a pretty intense macro and I noticed that it takes baout 30 seconds to run... So I did my redneck timing and put:

t1 = now()
t2 = now()
...

Then I put a break before the code ends to be able to see the constraints. I found that my macro runs in under 3 seconds if I delete this line of code:

Columns(a).Delete

Full context:

For a = ColumnCNT To 21 Step -1
        If Left(UCase(Cells(10, a).Value), 5) <> "TOTAL" Then
            'Columns(a).Delete
            totColumns = totColumns - 1
        ElseIf Cells(10, a).Interior.Color = RGB(238, 236, 225) Then
            totMonths = totMonths + 1
        End If
    Next a

Where ColumnCNT is set to 500. totColumns is set to ColumnCNT at the start. totMonths starts of as Zero (0).

If I allow that line of code it takes an average of 25 seconds to complete.

Question: Is there something I can do to speed up the deletion of columns?

- Matt

"If I must boast, I will boast of the things that show my weakness"

- Windows 2003 Server, 98 SE, XP
- VB.NET, VSTS 2010, ASP.NET, EXCEL VBA, ACCESS, SQL 2008

 

[tqeonline]

I was able to cut 6-8 seconds off by deleting all the conditional formatting (now down to about 17 for that section):

.FormatConditions.Delete

- Matt

"If I must boast, I will boast of the things that show my weakness"

- Windows 2003 Server, 98 SE, XP
- VB.NET, VSTS 2010, ASP.NET, EXCEL VBA, ACCESS, SQL 2008

 

[SkipVought]

application.screenupdating = false
application.calculation = xlCalculationManual

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[tqeonline]

Got it down to 11 by deleting the other sheets within the workbook.

    If Sheets.Count > 1 Then
        For Z = Sheets.Count To 2 Step -1
            Sheets(Z).Delete
        Next Z
    End If

- Matt

"If I must boast, I will boast of the things that show my weakness"

- Windows 2003 Server, 98 SE, XP
- VB.NET, VSTS 2010, ASP.NET, EXCEL VBA, ACCESS, SQL 2008

 

[tqeonline]

Hey Skip - I call two events for all my macros at the start and at the end:

Function Macro_Start()
    Application.ScreenUpdating = False
    Application.Calculation = xlCalculationManual
    Application.DisplayAlerts = False
    Application.DisplayStatusBar = False
    Application.EnableEvents = False
End Function
Function Macro_End()
    Application.DisplayAlerts = True
    Application.ScreenUpdating = True
    Application.DisplayStatusBar = True
    Application.EnableEvents = True
    Application.Calculation = xlCalculationAutomatic
    MsgBox "Finished"
End Function

- Matt

"If I must boast, I will boast of the things that show my weakness"

- Windows 2003 Server, 98 SE, XP
- VB.NET, VSTS 2010, ASP.NET, EXCEL VBA, ACCESS, SQL 2008