VBA and Excel: Why does my TRIM script results in #VALUE on large data sets?

Question

The following script works on smaller data sets (less than 30k rows or so), but results in "#VALUE" errors for every cell in the selected range when the range is larger than that.

Dim FirstCell As Range, LastCell As Range, MyRange As Range

Set LastCell = Cells(Cells.Find(What:="*", SearchOrder:=xlRows, _
      SearchDirection:=xlPrevious, LookIn:=xlValues).Row, _
      Cells.Find(What:="*", SearchOrder:=xlByColumns, _
      SearchDirection:=xlPrevious, LookIn:=xlValues).Column)

Set FirstCell = Cells(Cells.Find(What:="*", After:=LastCell, SearchOrder:=xlRows, _
      SearchDirection:=xlNext, LookIn:=xlValues).Row, _
      Cells.Find(What:="*", After:=LastCell, SearchOrder:=xlByColumns, _
      SearchDirection:=xlNext, LookIn:=xlValues).Column)

Set MyRange = Range(FirstCell, LastCell)
      MyRange.Select
      If MyRange Is Nothing Then Exit Sub
      Application.ScreenUpdating = False
      Application.Calculation = xlCalculationManual
    With Selection
        .Value = Evaluate("if(row(" & .Address & "),clean(trim(" & .Address & ")))")
    End With

    Application.ScreenUpdating = True
    Application.Calculation = xlCalculationAutomatic
    MsgBox "Finished trimming " & vbCrLf & "excess spaces", 64

VBA TRIM Error

Answer 1

I managed to replicate your issue, and using a variant array as shown below overcomes the issue for large data sets

Dim FirstCell As Range, LastCell As Range, MyRange As Range
Dim DataRange() As Variant
Dim lRows As Long
Dim lCols As Long
Dim i As Long, j As Long
Dim value As String

Set LastCell = Cells(Cells.Find(What:="*", SearchOrder:=xlRows, _
      SearchDirection:=xlPrevious, LookIn:=xlValues).Row, _
      Cells.Find(What:="*", SearchOrder:=xlByColumns, _
      SearchDirection:=xlPrevious, LookIn:=xlValues).Column)

Set FirstCell = Cells(Cells.Find(What:="*", After:=LastCell, SearchOrder:=xlRows, _
      SearchDirection:=xlNext, LookIn:=xlValues).Row, _
      Cells.Find(What:="*", After:=LastCell, SearchOrder:=xlByColumns, _
      SearchDirection:=xlNext, LookIn:=xlValues).Column)

Set MyRange = Range(FirstCell, LastCell)
  MyRange.Select
  If MyRange Is Nothing Then Exit Sub
  Application.ScreenUpdating = False
  Application.Calculation = xlCalculationManual
  lRows = MyRange.Rows.Count
  lCols = MyRange.Columns.Count
  ReDim DataRange(1 To lRows, 1 To lCols)
  DataRange = MyRange.value
  For j = 1 To lCols
    For i = 1 To lRows
      DataRange(i, j) = Trim(DataRange(i, j))
    Next i
  Next j

  MyRange.value = DataRange

Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic
MsgBox "Finished trimming " & vbCrLf & "excess spaces", 64

For reference, I used this article to help come up with the answer: https://blogs.office.com/2008/10/03/what-is-the-fastest-way-to-scan-a-large-range-in-excel/