code optimization help

[Elrophin]

Hello Mr. Excel forum, I use the code below for formating reports that come from cloud based platforms are have extrahidden html code within the cells (specifically this hidden info CHR 0160, as a space (CHR 032)), and also set some standard formatting on the sheet once the clean up action is performed.

Over the years I added more formatting standards to the process, such as autoset filters, highlight the first row and such... the issue is that has made the code much, much slower than it used to be and I dont have the skills to optimize it. I am looking for help here to do just that.

Woudl anyone be able to optimize this code so that it still does what is intended but faster?

Sub A0___FormatSheet()

   
      Application.DisplayAlerts = True
      Application.EnableEvents = True   'should be part of Change Event macro


' not part of original remove if error
    Range("A1").Select 'select indicated cell
    Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select 'crtl+Shift+end selects all active cells until last cell on the edge
    Selection.UnMerge 'unmerge
    Selection.WrapText = False 'unwrap
    Selection.Columns.AutoFit 'autofit columns
    Selection.EntireRow.Hidden = False 'unhide
    Selection.Rows.AutoFit 'autofit rows
    ActiveWindow.DisplayGridlines = False 'remove gridlines
    Rows("2:2").Select
    ActiveWindow.FreezePanes = True 'freeze selected row
    Range("A1").Select 'select indicated cell
    Selection.AutoFilter 'set filter on row selected
    Range("A1").Select 'select indicated cell
    Range(Selection, Selection.End(xlToRight)).Select
    Selection.Interior.ColorIndex = 43 'set green color
    Range("A1").Select 'select indicated cell  'selects all again so trim is done on selection
    Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select 'crtl+Shift+end selects all active cells until last cell on the edge
' not part of original remove if error

   If Application.Calculation = xlCalculationManual Then
      MsgBox "Calculation was OFF will be turned ON upon completion"
   End If
   Application.ScreenUpdating = False
   Application.Calculation = xlCalculationManual
   Dim Cell As Range
   'Also Treat CHR 0160, as a space (CHR 032)
   Selection.replace what:=Chr(160), Replacement:=Chr(32), _
     LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False
   'Trim in Excel removes extra internal spaces, VBA does not
   On Error Resume Next   'in case no text cells in selection
   For Each Cell In Intersect(Selection, _
      Selection.SpecialCells(xlConstants, xlTextValues))
     Cell.Value = Application.Trim(Cell.Value)
     Cells.SpecialCells(xlCellTypeFormulas, xlErrors).Clear 'clear #na
   Next Cell
   On Error GoTo 0
   Application.Calculation = xlCalculationAutomatic
   Application.ScreenUpdating = True
   ActiveSheet.Range("a2").Select 

   
End Sub

 

[Fluff]

Hi & welcome to MrExcel.
How about

Sub A0___FormatSheet()

   If Application.Calculation = xlCalculationManual Then
      MsgBox "Calculation was OFF will be turned ON upon completion"
   End If

   With Application
      .DisplayAlerts = False
      .EnableEvents = False   'should be part of Change Event macro
      .ScreenUpdating = False
      .Calculation = xlCalculationManual
   End With
   ActiveWindow.DisplayGridlines = False 'remove gridlines
   ActiveWindow.FreezePanes = False
   
' not part of original remove if error
   With Range("A1", Cells.SpecialCells(xlLastCell))
      .UnMerge 'unmerge
      .WrapText = False 'unwrap
      .Columns.AutoFit 'autofit columns
      .EntireRow.Hidden = False 'unhide
      .Rows.AutoFit 'autofit rows
      .Rows(1).Interior.ColorIndex = 43
      .Rows(1).AutoFilter
      .Replace Chr(160), " ", xlPart, , , , False, False
      .Value = Evaluate("trim(" & .Address & ")")
      On Error Resume Next
      .SpecialCells(xlFormulas, xlErrors).ClearContents
      On Error GoTo 0
   End With
   Range("A2").Select
   ActiveWindow.FreezePanes = True 'freeze selected row
   
   With Application
      .DisplayAlerts = True
      .EnableEvents = True   'should be part of Change Event macro
      .Calculation = xlCalculationAutomatic
   End With
End Sub