Remove only one space, if there is a space, from the end of text.

[pglufkin]

Was wondering if someone could help me.

Was thinking something along the lines of the procedure below, but I only want to remove one space at the end of my text within a cell, if there is a space. Any quick ideas? Thx.

Sub hth() 
    Dim c As  Range 
     
    For Each c In Range("H1", Range("H" & Rows.Count).End(xlUp)) 
        c.Value = Trim(c.Value) 
    Next c 
End Sub

 

[Marcelo Branco]

It is simply a "stand-in" character for the address (you can use any character that will not be included in the argument's text) that gets replaced all at once by the Replace function... it avoids having to concatenate the address into the argument's text string over and over again which makes constructing/reading the argument a little easier.

Thank you for the explanation!

I'll try to use in the future.

M.

 

[Rick Rothstein]

Thank you for the explanation!

I'll try to use in the future.

Actually, the idea to do that was not original with me, but I cannot remember who introduced me to the idea... I think it was one of the Peters in this forum (maybe Peter_SSs?)... I tried Google'ing it, but could not find a satisfactory search string. Anyway, I though it was a good idea when I first was told about it and have tried to remember to adopt it in my own code (but old habits die hard and often I find myself doing what I had always done previously... concatenating like crazy all over the place ).

 

[Marcelo Branco]

In fact i'm not sure i fully understood the concept.

@ sustitutes the address, not the value, is it?

Probably when i try to use it in the future i'll make blunders

But i dont know other ways to learn new things - trial and error, trial and error...

M.

 

[Scott Huish]

I'm curious as to why you wouldn't want to remove all the spaces from the end of the cell.