Using the VBA Trim function

[jgj1988]

Hello,


Im looking to use the VBA RTrim function to remove spaces from the end of the cell while preserving the spaces that exist inside the cell text. For example some cells look like this(_ as spaces) ABC__115____ I want to remove the spaces after the 5 but keep the spaces between c and 1.


I played with this code i found but it replaces all the values in my column d with the #value error. Can anyone help me?


Dim rng As Range

On Error Resume Next 'if entire column is blank, exit sub

Set rng = Intersect(Range("d1").EntireColumn, ActiveSheet.UsedRange)

rng.Value = Evaluate("IF(ROW(" & rng.Address & "),IF(" & rng.Address & _

"<>"""",rTRIM(" & rng.Address & "),""""))")

 

[SirJB7]

Hi, jgj1988!


Excel TRIM function removes all leading and trailing spaces and keeps only one internal space for occurrences any number of spaces.

VBA TRIM function removes only all leading and trailing spaces and keep all internal spaces for any occurrence of any number of spaces.


So your formula usage will retrieve a 7 char length string "ABC 115" (unquoted), 1 space between ABC and 115.

And the following code will retrieve a 8 char length string "ABC 115", all original spaces (2) between ABC and 115.

-----

rng.Value = Trim("XXX")

-----

where "XXX" is the original string.


Hope it helps.


Regards!

 

[Debraj]

Hi, jgj1988!


I think you are mixing EXCEL FORMULA with VB FUNCTION.


RTRIM is a VB function.. and you are using this in EVALUATE.. which works with EXCEL FORMULA


Please use RTIM as a VB FUNCTION or concatenate the complete formula is such way that.. you can manage both feature correctly.


for the time being use below code.. I will try to remove the loop.. if required.

[pre]

Dim rng As Range
On Error Resume Next 'if entire column is blank, exit sub
Set rng = Intersect(Range("d1").EntireColumn, ActiveSheet.UsedRange)

For i = 0 To rng.Rows.Count
If rng(i) <> "" Then rng(i) = RTrim(rng(i))
Next i

[/pre]

Regards,

Deb