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

Are you setting `Application.ScreenUpdating = False` before you run this? That can drastically speed up VBA code. Just don't forget to set `Application.ScreenUpdating = True` at the end of your code. — TylerH, Feb 09 '17 at 22:19
@TylerH the main bottleneck here is reading and writing individual cells, but if you're going to start changing `ScreenUpdating`, you should at least restore it to whatever it *was* before your procedure hijacked it. — ThunderFrame, Feb 09 '17 at 22:25
@ThunderFrame Yes, hence why I said set it back to true at the end of your code. — TylerH, Feb 09 '17 at 22:39
@TylerH But what if it wasn't True when *entering* the procedure? Explicitly setting it to `True` at the end of your code might create unexpected results for procedures that call this procedure. — ThunderFrame, Feb 09 '17 at 22:48
@ThunderFrame My comment is intended to be read in its entirety. So clearly the context is that you would set screen updating to true to reverse the setting of screen updating to false before the relevant code runs. Context is key. — TylerH, Feb 09 '17 at 23:39
@TylerH I see, I read " at the end of your code" as if you meant "at the end of the *current* procedure". — ThunderFrame, Feb 09 '17 at 23:58
By the way, VBA's `Trim` and excel's `Application.Trim` (aka `WorksheetFunction.Trim` do not do the same trimming. The former trim only trailing spaces (at beginning and end), and the latter trims also inside the string, removing any multiple spaces and keeping only one space... which makes the two answers different on more aspects than we thought... — A.S.H, Feb 15 '17 at 02:21

ThunderFrame · Accepted Answer · 2017-02-09T22:34:06.603

12

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

edited Feb 09 '17 at 22:34

answered Feb 09 '17 at 22:16

ThunderFrame

9,352
2
29
60

Thank you very much! way way way faster! – Leandro Moreira Feb 09 '17 at 22:44
but i dont have formulas, can i simply remove the v? – Leandro Moreira Feb 09 '17 at 22:47
It shouldn't matter, but if you want to be more explicit, just change both usages of `.Formula` to `.Value` – ThunderFrame Feb 09 '17 at 22:58
nice code. Using Value2 rather than Formula when reading and writing would add a smidgeon further speed – brettdj Feb 10 '17 at 05:50

A.S.H · Answer 2 · 2017-02-10T16:42:10.167

8

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

edited Feb 10 '17 at 16:42

answered Feb 09 '17 at 22:38

A.S.H

29,101
5
23
50

3

This is faster than my answer, and in the event that the range contains formulas, you could substitute the instances of `.Value` for `.Formula`, and preserve the formulas. – ThunderFrame Feb 09 '17 at 22:56

score 0 · Answer 3 · answered Oct 29 '20 at 09:48

0

For some reason Worksheetfunction.Trim does not remove the spaces, maybe because it has CRLF in it. In those cases Application.Clean function is the best method to choose.

answered Oct 29 '20 at 09:48

Attila

118
1
8

TRIM function/remove spaces from cells using VBA

3 Answers3

Linked