trim, chr(10)

  • Thread starter Thread starter Steve
  • Start date Start date
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.
 
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...... ;-)
 
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...... ;-)
Click to expand...
 
An ASCII 9 is a Tab character... change the vbLf in my original code to
vbTab and see if that works for you.
 
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.
 
Back
Top