Trim selected range too slow - how to increase process speed

Question

below script keep running for more then a minutes. is there any fastest way to trim selected range?

Sub Trim_Selection()

    Dim A As Range

    Set A = Selection

    For Each cell In A
    cell.value = WorksheetFunction.Trim(cell)
    Next

End Sub

Answer 1

Try use Variant array to do the transform.

Sub Trim_Selection()

    Dim vArr As Variant
    Dim row As Long, col As Long
    Dim i As Long, j As Long

    vArr = Selection.Value
    If VarType(vArr) >= 8192 Then
        'it is array
        row = Selection.Rows.Count
        col = Selection.Columns.Count
        For i = 1 To row
            For j = 1 To col
                vArr(i, j) = Trim(vArr(i, j))
            Next j
        Next i
    Else
        'it is single cell
        vArr = Trim(vArr)
    End If
    
    Selection = vArr

End Sub