VBA for - If the last character in a cell is a space, delete it. Do this for a whole column except row 1

[VBAProIWish]

Hello all,

After 1 hour of frustrating searching, I gave up and decided to go to the experts. I got every answer known to mankind except the one that I need.

In column B, I have several rows of data. They can be any combination and in any order of numbers, letters, dashes and spaces.

For example,
I might have 125-2D3 , but it has a space at the end, so it's actually 125-2D3"space"

I could also have D25 429T"space" (note that there is also a space between the "5" and the "4", but I don't want that deleted), only the space at the end.

I need VBA code that only removes any space after the last non-space character, but I need to apply it to an entire column (except row 1)

I suppose you could create the code to delete all spaces after the last non-space character, but I think I will be ok with it only searching for one space.

Thanks much

 

[Rick Rothstein]

VBA has a function named RTrim which will trim spaces from the right side of text. So, if you have this...

Txt = "   text    "

doing this...

Txt = "   text    "
Txt = RTrim(Txt)

will make Txt contain

 "   text"

 

[VBAProIWish]

Hello Rick and thanks for the response. How do I tell column B to remove the right-most space from each cell without leaving a formula behind if there is one? Is there code for that?

Thanks much

 

[Rick Rothstein]

I'm not 100% sure I understand. Are you saying you have a formula in the cells that is putting text into your cells and that text has a trailing space? Or are you asking how to convert any formulas into constants and if that constant has a trailing space, remove it?

 

[VBAProIWish]

If there is a space, simply delete it. No resulting formula. I presently have no formula in the column and want to keep it that way. I have text that has a trailing space. I need VBA code to do this.
In other words
Select column B
if the last character in each cell in column B is a space, delete it.

That's it

Thanks

 

[Rick Rothstein]

Give this macro a try....

Sub RightTrimCellsInColumnB()
  Dim LR As Long
  LR = Cells(Rows.Count, "B").End(xlUp).Row
  Range("B2:B" & LR) = Evaluate(Replace("IF(B2:B#="""","""",LEFT(B2:B#,FIND(""|"",SUBSTITUTE(B2:B#,RIGHT(TRIM(B2:B#)),""|"",LEN(B2:B#)-LEN(SUBSTITUTE(B2:B#,RIGHT(TRIM(B2:B#)),""""))))))", "#", LR))
End Sub

 

[VBAProIWish]

Rick, that worked absolutely perfectly!
I am amazed that so much code (relatively speaking) is required for what is seemingly such a simple action to me, but you know this stuff WAY better than me, so I will just continue graciously and humbly learning.

Thanks very much!

 

[Rick Rothstein]

The solution I gave you used no loops, so it was a little on the longish side. Here is a looping version which is shorter but I think (untested) slower...

Sub RightTrimCellsInColumnB()
  Dim Cell As Range
  For Each Cell In Range("B1", Cells(Rows.Count, "B").End(xlUp))
    Cell.Value = RTrim(Cell.Value)
  Next
End Sub