Thoughts to speed up this macro?

[billfinn]

Good morning!
I have a macro that functions as it should but is slow. The macro strips out all of the report and page headers from the text file dumped from the AS400 and organizes the sheet in the desired manner. I realize that part of the reason it's slow is the size of the worksheet it's formatting (62,743 rows) but I'm hoping some of you folks might have ideas that would help me to speed this process up? All suggestions and comments are very much appreciated.
Thanks much!
Bill

Sub Strip_Page_Report_Headers()

    'Macro to strip out Report and Page headings to leave an Excel File with columns properly formatted.
    On Error GoTo ErrorHandler
Application.EnableEvents = False
    
    Windows("Estimator.xlsm").Activate  'Go to the Estimating Tool
    Sheets("Item Master").Select      'Select the Test Data worksheet in the Estimating tool
    Selection.EntireColumn.Hidden = False
    Dim Ary As Variant
    Dim i As Long
    Ary = Array("*QUERY*", "*INVMASTB*", "*INVMASTAAA*", "*LIBRARY*", "*PRICEMSM*", "*DATE*", "TIME*", "*info*", "*PAGE*", "Pricing", "*Cost*", "*Page*", "*DELETED*", "*EDIORGI*", "*DELETE*", "*FORMAT*")
    For i = 0 To UBound(Ary)
    Range("A:O").Replace Ary(i), "=xxx", xlWhole, , False, , False, False
    Next i


      
For i = 1 To 15
   On Error Resume Next
   Columns(i).SpecialCells(xlFormulas, xlErrors).EntireRow.Delete
   On Error GoTo 0
Next






'Add Column Labels
Rows(1).Insert Shift:=xlDown
    Range("A1").Select
    ActiveCell.FormulaR1C1 = "ITEM"
    Range("B1").Select
    ActiveCell.FormulaR1C1 = "DESCRIPTION"
    Range("C1").Select
    ActiveCell.FormulaR1C1 = "COST"
   
    ActiveCell.FormulaR1C1 = "Price"
  ' Set Column C as currency
Worksheets("Item Master").Columns("C").NumberFormat = "$#,##0.00_);[Red]($#,##0.00)"


Columns.AutoFit
  
ErrorHandler:
    Application.EnableEvents = True


End Sub

 

[JoeMo]

At the start of your code, replace Application.EnableEvents = False with this:

With Application
    .EnableEvents = False
    .ScreenUpdating = False
    .Calculation = xlCalculationManual
End With

and at the end replace Application.EnableEvents = True with this:

With Application
    .EnableEvents = True
    .ScreenUpdating = True
    .Calculation = xlCalculationAuotomatic
End With

 

[Joe4]

Also, removing "Select" statements will speed up your code. Almost any time you have a row that ends with "Select" followed by a row that begins with "Selection" or "ActiveCell", you can combine those two rows together.

So all of this:

    Range("A1").Select
    ActiveCell.FormulaR1C1 = "ITEM"
    Range("B1").Select
    ActiveCell.FormulaR1C1 = "DESCRIPTION"
    Range("C1").Select
    ActiveCell.FormulaR1C1 = "COST"

can be reduced to this:

    Range("A1").FormulaR1C1 = "ITEM"
    Range("B1").FormulaR1C1 = "DESCRIPTION"
    Range("C1").FormulaR1C1 = "COST"

 

[billfinn]

JoeMo and Joe4,
Thanks so much for the input. I am very grateful. Waiting on a new text file and a test. I will report results later
Thanks,
Bill

 

[Joe4]

You are welcome.
Glad "the Joes" were able to help!

 

[billfinn]

JoeMo and Joe4,
Just wanted to get back to you both with an update. The old Elapsed time for this macro was around 2 minutes 54 seconds. New elapsed time is 1 minute 56 seconds. For a file this large that is a significant improvement!
Thanks again for your suggestions.
Bill

 

[JoeMo]

JoeMo and Joe4,
Just wanted to get back to you both with an update. The old Elapsed time for this macro was around 2 minutes 54 seconds. New elapsed time is 1 minute 56 seconds. For a file this large that is a significant improvement!
Thanks again for your suggestions.
Bill

As Joe said (Joe Who?) - Glad "the Joes" were able to help!