Trim Column in VBA

[Blister]

Hello,

I'm looking for a way apply the Trim function to a column ranging from cell 2 to the last active cell in the column. In this case the data starts at E2.

 

[DushiPunda]

Can you provide a sample of your data and a sample of the end results you are seeking?

 

[Blister]

My data varies wildly, I'm just looking to trim the spaces from the beginning and ends of the data. This is what the =Trim(text) function does.

 

[DushiPunda]

Would probably be best to accomplish this via VBA.

Sub TrimText()
Dim lRow As Integer

With Worksheets("Sheet1")
    lRow = .Range("E2").End(xlDown).Row
    
    For i = 2 To lRow
        .Cells(i, "E").Value = Trim(.Cells(i, "E").Value)
    Next i
End With
End Sub

 

[Blister]

Exactly what I needed, thank you!

 

[Rick Rothstein]

For those who might read this thread in the future... DushiPunda's solution will only trim outside space. If you want to apply Excel's TRIM function which does that plus it collapses multiple adjacent internal spaces down to single spaces, then you can use this macro...

Sub TrimColumnE()
  Dim Addr As String
  Addr = "E1:E" & Cells(Rows.Count, "E").End(xlUp).Row
  Range(Addr) = Evaluate("IF(" & Addr & "="""","""",TRIM(" & Addr & "))")
End Sub

 

[footoo]

For those who might read this thread in the future... DushiPunda's solution will only trim outside space. If you want to apply Excel's TRIM function which does that plus it collapses multiple adjacent internal spaces down to single spaces, then you can use this macro...

Sub TrimColumnE()
  Dim Addr As String
  Addr = "E1:E" & Cells(Rows.Count, "E").End(xlUp).Row
  Range(Addr) = Evaluate("IF(" & Addr & "="""","""",TRIM(" & Addr & "))")
End Sub

Or DushiPunda's code could be changed to :
.Cells(i, "E").Value = Application.Trim(.Cells(i, "E").Value)

 

[Rick Rothstein]

Or DushiPunda's code could be changed to :
.Cells(i, "E").Value = Application.Trim(.Cells(i, "E").Value)

True, but the reason I did not suggest that is I wanted to offer a solution that did not make use of a loop.

 

[footoo]

True, but the reason I did not suggest that is I wanted to offer a solution that did not make use of a loop.

The reason for my suggestion was for completeness "for those who might read this thread in the future"

Normally, the only reason to avoid a worksheet object loop is to reduce run-time - any such reduction is often immaterial.
In the event it is material, another approach could be to put the range in an array, loop the array, and put the result back to the worksheet - this could well be quicker in some circumstances than using Evaluate.

The Evaluate method could also be written like this (although probably a bit slow) :

Sub TrimColumnE()
[E:E] = [IFERROR(TRIM(E:E),"""")]
End Sub

 

[Rick Rothstein]

Normally, the only reason to avoid a worksheet object loop is to reduce run-time - any such reduction is often immaterial.

That depends highly on how the loop is written and/or what the loop is doing... it is definitely not the case with this OP's request (see below).

In the event it is material, another approach could be to put the range in an array, loop the array, and put the result back to the worksheet - this could well be quicker in some circumstances than using Evaluate.

Not always... it depends on what the code is doing. You might be surprised to find out that the Evaluate method is faster than the "put the range in an array, loop the array, and put the result back" method you suggested, not enough time difference to matter in human terms, but Evaluate was definitely faster (on my computer, of course). I Tested with 200,000 cells in a column... Evaluate took 0.74 seconds whereas the array method took 1.13 seconds. Here is the array code that I used...

Sub TrimColumnE()
  Dim R As Long, Data As Variant
  Data = Range("E1", Cells(Rows.Count, "E").End(xlUp))
  For R = 1 To UBound(Data)
    Data(R, 1) = Application.Trim(Data(R, 1))
  Next
  Range("E1").Resize(UBound(Data)) = Data
End Sub

I think the reason why the array method was slower was because of the repeated calls to the Application.Trim function. By comparison, the loop code suggested earlier by DushiPunda was taking so long that I stopped it and measured what had taken place up to then... after 7.5 minutes, the loop had processed only 91,357 rows of the 200,000 that were there... and I had turned off ScreenUpdating for DushiPunda's code in anticipation of its lengthy runtime. By the way, we know the OP does not have anywhere near 200,000 rows of data because he said DushiPunda's code worked fine for him and that code was limited to a maximum of 32,767 rows because the lRow variable was declared as Integer (I had to change it to Long in order to process the 200,000 rows for my test). The reason this loop code was so slow is because it visits and processed each cell individually one-at-a-time... interacting with the worksheet is a real time killer and that code maximized how many times it did this.

The Evaluate method could also be written like this (although probably a bit slow) :

Sub TrimColumnE()
[E:E] = [IFERROR(TRIM(E:E),"""")]
End Sub

It took 2.6 seconds for this code to process the 200,000 rows of data.