Trim Function

[nbob]

I have Column J2 and K2. J2 contains first names in my database; K2 Last Names. Unfortunately when the data is sent to me all the names have about 5-9 extra spaces which need to go. Currently I can select a cell and use =trim(k2) drag this down the list of 18000+ rows and then copy>past special>values in the original column but i know there has got to be an easier way. I know the following does not work...at all.

Dim LResult As String
LResult = Trim("k2:k19000")

Any ideas?

 

[Jonmo1]

Try

Sub TrimRange()
Dim C As Range
 
For Each C In Range("K2:K19000")
    C.Value = Trim(C.Value)
Next C
End Sub

 

[Smitty]

How's this:

<font face=Calibri><SPAN style="color:#00007F">Sub</SPAN> foo()<br>    <SPAN style="color:#00007F">Dim</SPAN> LR <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN><br>        LR = Cells(Rows.Count, "K").End(xlUp).Row<br>        <br>        <SPAN style="color:#00007F">With</SPAN> Range("L1:L" & LR)<br>            .FormulaR1C1 = "=Trim(RC[-1])"<br>                 <SPAN style="color:#00007F">With</SPAN> Range("K1:K" & LR)<br>                    .Value = Range("L1:L" & LR).Value<br>                    .Value = .Value<br>                <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN><br>            .Value = ""<br>        <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN><br>    <br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN></FONT>

I'm sure there's a better way, but it's been a long day, and my brain hurts...

 

[nbob]

awesome! Works. saved me huge headaches. Tequila anyone?