TRIM function/remove spaces from cells using VBA

Question

I am using the code below to trim some "blank cells" that contain a space. The thing is it takes too much time, as is looping to every cell. What I want is to remove the spaces(the ones in the beginning and end, not middle), of all the cells.

Is there any easier way that I can apply all at once?

For a = 1 To ScenarioTableLastRow
    For f = 1 To ScenarioTableLastColumn

       If Cells(a, f) <> "" Then
            Cells(a, f) = Excel.Application.Trim(Cells(a, f))
       End If

    Next f
Next a

Answer 1

You'll get much better performance copying the data into an array, and working on the array, then placing the data back into the range.

Also, don't use Excel.Application.Trim. That's Excel 95 syntax, and a late-bound call with unexpected error handling. VBA has a Trim function built-in - it's about 10 times faster and it provides Intellisense.

Sub test()

    'Assuming ScenarioTable is a range
    Dim ScenarioTable As Range
    Set ScenarioTable = Range("ScenarioTable")

    'I assume your range might have some formulas, so...
    'Get the formulas into an array
    Dim v As Variant
    v = ScenarioTable.Formula

    Dim a As Long
    Dim f As Long
    'Then loop over the array
    For a = LBound(v, 1) To UBound(v, 1)
        For f = LBound(v, 2) To UBound(v, 2)
            If Not IsEmpty(v(a, f)) Then
                v(a, f) = VBA.Trim(v(a, f))
            End If
        Next f
    Next a
    'Insert the results
    ScenarioTable.Formula = v
End Sub

Answer 2

Do it on the whole range at once using the array version of Excel's Trim:

myRange.Value = Application.Trim(myRange.Value)

Using the only variables visible in your code, it would be:

With Range(Cells(1,1), Cells(ScenarioTableLastRow, ScenarioTableLastColumn))
     .Value = Application.Trim(.Value)
End With