Fastest Way to Delete Columns

[nniedzielski]

I am running a macro that will take a worksheet weekly and drill down on the data.

This worksheet will always have columns A:U. I am deleting a good portion of these with this code:

Range("B:B,F:F,H:L,N:T").EntireColumn.Delete

Unfortunately this line of code alone takes roughly 7 to 8 seconds to delete these columns.

I am running this to help the speed:

Application.DisplayAlerts = False
Application.ScreenUpdating = False

Is there a faster way to get the code to run possibly using variables or something that would make it go faster when running?

heres the code:

Dim i As Integer
Dim minDate As Date
Dim wsName As String




    Application.DisplayAlerts = False
    Application.ScreenUpdating = False




        Sheets("Weekly Static Routes").Select


        Range("B:B,F:F,H:L,N:T").EntireColumn.Delete
        Range("A1") = "TMS #"
        Range("C1") = "Carrier"
        Range("D1") = "Pickup Date & Time"
        Range("E1") = "Shipper"
        Range("F1") = "Consignee"
        Range("D:D").NumberFormat = "mm/dd/yyyy hh:mm"
        Range("E:E").Select
            With Selection
                Selection.NumberFormat = "General"
                .Value = .Value
            End With
        Worksheets("Weekly Static Routes").Range("A:G").Columns.AutoFit
        
        Worksheets("Weekly Static Routes").Select
        ActiveWorkbook.Worksheets("Weekly Static Routes").Sort.SortFields.Clear
            ActiveWorkbook.Worksheets("Weekly Static Routes").Sort.SortFields.Add Key:= _
                Range("E:E"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption _
                :=xlSortNormal
            With ActiveWorkbook.Worksheets("Weekly Static Routes").Sort
                .SetRange Range("A1:G10000")
                .Header = xlYes
                .MatchCase = False
                .Orientation = xlTopToBottom
                .SortMethod = xlPinYin
                .Apply
            End With
        
        
        ActiveSheet.Range("A:G").AutoFilter Field:=5, Criteria1:="<>1"
        lr = Cells(Rows.Count, 1).End(xlUp).Row
        If lr > 1 Then
            Range("A2:A" & lr).EntireRow.Delete
        End If
        Selection.AutoFilter

thank you,

 

[mole999]

turn off calculation and then turn it back on, would seem you have formulas over the cells, and each will evaluate

 

[nniedzielski]

how would i do that?

 

[svendiamond]

Application.Calculation = xlCalculationManual
'your code here
Application.Calculation = xlCalculationAutomatic

 

[nniedzielski]

adding in this code was actually a bit slower.

6.02 seconds without the calculation
6.33 with your code wrapped around mine

any other ideas?

 

[Sektor]

If you have Worksheet_Change macro, then try to turn off events: Application.EnableEvents = False and then turn them back on.

 

[svendiamond]

Can you just hide the columns rather than delete them?

 

[nniedzielski]

Sektor - Enablevents ran at 6.1 seconds, which is a little slower

svendiamond - i would really prefer to have all the information i dont need deleted.

 

[Sektor]

May be not to delete, but clear these columns (ClearContents)?

 

[nniedzielski]

if i clear them out, which is an option, i still want all my data sorted out nicely, how would i accomplish that?