VBA - Trim function : reduce time of operation / freezing

Question

I have written code in VBA that removes some potential spaces between characters. The code works pretty well but becomes really slow when the file contains thousands of rows. I'd like to know if it's possible to improve it, in order to reduce the time of operation, but also mainly to stop the file from freezing. Here is the code:

Sub Test()
  Dim cell as Range
  Dim sht As Worksheet
  Dim LastRow As Long
  Dim StartCell As Range
  Dim areaToTrim As Range
  Set sht = ThisWorkbook.Worksheets("SS upload")
  Set StartCell = sht.Range("A14")
  LastRow = sht.Cells(sht.Rows.Count, StartCell.Column).End(xlUp).Row
  Set areaToTrim = sht.Range("B14:B" & LastRow)
  For Each cell In areaToTrim
    cell.Value = Trim(cell.Value)
  Next cell
End Sub

Answer 1

The fastest way is to read the range into an array, trim it there and then write it back to the range:

Sub Test()

  Dim sht As Worksheet
  Dim LastRow As Long
  Dim StartCell As Range
  Dim areaToTrim As Range
  Dim varArray() As Variant
  Dim i As Long

  Set sht = ThisWorkbook.Worksheets("SS upload")
  Set StartCell = sht.Range("A14")
  LastRow = sht.Cells(sht.Rows.Count, StartCell.Column).End(xlUp).Row
  Set areaToTrim = sht.Range("B14:B" & LastRow)
  varArray = areaToTrim ' Read range into array
  For i = LBound(varArray, 1) To UBound(varArray, 1)
    varArray(i, 1) = Trim(varArray(i, 1))
  Next i
  areaToTrim.Value = varArray ' Write array back to range

End Sub

No need to worry about Application.ScreenUpdating or Application.Calculation. Nice and simple!

If you are still worried about any responsiveness, put a DoEventsin the body of the loop.