trim, chr(10)

[Steve]

Hi again.
Rick helped me find that it was actually chr 9. Your code worked once I got
that, so just wanted you to know.....
Again-- thank you for your help! It's deeply appreciated.
Best.

 

[Steve]

Sub replaceTrim()

' replaceTrim Macro

Range("J14:J21").Select

Selection.Replace What:=Chr(9), Replacement:="", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False

End Sub

Based on Rick's final post, I learned that it was chr 9. Once I got that,
this worked famously!!!!
Darn pesky non-descript character numbers. where's my shotgun.... oh, wait,
that's my computer... sigh...... ;-)

 

[Dave Peterson]

VBA has some constants that make the code a bit easier to read.

vbTab (chr(9))
vbLf (chr(10))
vbCr (chr(13))

Chip Pearson has a very nice addin that will help determine what characters are
in the cell:
http://www.cpearson.com/excel/CellView.aspx

Sub replaceTrim()

' replaceTrim Macro

Range("J14:J21").Select

Selection.Replace What:=Chr(9), Replacement:="", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False

End Sub

Based on Rick's final post, I learned that it was chr 9. Once I got that,
this worked famously!!!!
Darn pesky non-descript character numbers. where's my shotgun.... oh, wait,
that's my computer... sigh...... ;-)

 

[Rick Rothstein]

An ASCII 9 is a Tab character... change the vbLf in my original code to
vbTab and see if that works for you.

 

[Steve]

Ah, that figures....
Thinking about the original data further, that actually makes sense.
The original data was a 50 line data set, with tabbed spacing for organizing
it on a map.
Nice tool that cell view. One I'll be keeping.
Thanks for the heads up on it.