VBA and Excel: Why does my TRIM script result in #VALUE on large datasets?

Question

The following script works with smaller data sets (less than 30 thousand rows or so), but leads to "#VALUE" errors for each cell in the selected range when the range is larger than this.

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 problem, and using an alternative array as shown below overcomes the problem for large datasets

 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 find the answer: https://blogs.office.com/2008/10/03/what-is-the-fastest-way-to-scan-a-large-range-in- excel /