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

About how many cells are you changing with this code? I just tested it on ~1.7 million cells and I didn't get any errors — TMH8885, Feb 02 '16 at 20:48
8,106,860 cells on my practice document. That's 161,363 rows. — Joseph Delli Gatti, Feb 02 '16 at 20:50
It seems to work up to about 70k rows. Too much more than that, it just sets the value of every cell in the selected range to #VALUE. — Joseph Delli Gatti, Feb 02 '16 at 21:01
What about copying the range (or at least sections of it) into a memory-based array, performing the trim, then copy that block back to the spreadsheet? Would probably be faster too. — PeterT, Feb 02 '16 at 21:38
I also tried setting MyRange = Selection (where I select the same range as the FirstCell to LastCell), and it works about half the time without giving me the same error. — Joseph Delli Gatti, Feb 02 '16 at 21:44
Maybe a DoEvents [using this method](http://stackoverflow.com/questions/11277034/wait-until-application-calculate-has-finished) might help? Have you also tried moving `ScreenUpdating` after you set the calculation property? — Dan, Feb 23 '16 at 20:56
Perhaps try iterating through all columns in the range and performing the action on each column successively? — Nick Peranzi, Feb 27 '16 at 06:14

TrtlBoy · Answer 1 · 2016-03-07T23:47:17.293

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/

Links can be broken in the future. Please copy-paste the information needed to answer the question into your post.Use the link only as a reference. — eirikdaude, Mar 07 '16 at 10:16
I have updated the answer to make it more clear that the link was for reference only, the answer is included above — TrtlBoy, Mar 07 '16 at 23:08

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

1 Answers1