Test condition never satisfied in loop
 

What is wrong with the loop condition in this code?

The purpose of the code is to remove all the spaces before the first
character in a column of selected worksheet cells like this:

F
1
R
H
T
U
N
25.05
8.80
35.38
27.50
24.25
26.00
27.50
22.88

where each row is a value in a cell of a column in the worksheet.

When I run the code on these cell values, the test condition, testStr =
Left(nxtChar, 1), is never satisified.

Thank you.

John Wirt

Public Sub RemoveSpace()
Dim nxtChar As String, testStr As String
Dim rngTxt As Range
Dim rw As Range

testStr = Left(" ", 1)
Set rngTxt = Selection
For Each rw In rngTxt.Rows
nxtChar = rw.Cells(1, 1).Value
Do
nxtChar = Mid(nxtChar, 2, Len(nxtChar) - 1)
Loop While testStr = Left(nxtChar, 1)
rw.Value = nxtChar
Next
End Sub

Test condition never satisfied in loop
 

What are the "160s" in the original text strings?

Hi. Glad it's working for you. :)

I believe the 160's are Nonbreaking spaces (&nbsp).

Well, I was just now debating on whether to remove the Clean function.
It's been a while, so I just decided to read the Help on Clean in Excel
2007.
Excel 2007 has a little more to say on the subject.

It actually now mentions the '160' problem. However, it also has this to
say.

....The CLEAN function was designed to remove the first 32 non-printing
characters in the 7 bit ASCII code (values 0 through 31) from text. In the
Unicode character set, there are additional nonprinting characters (values
127, 129, 141, 143, 144, and 157). By itself, the CLEAN function does not
remove these additional nonprinting characters.

So, it looks like we should modify the code now to remove these additional
characters also.
I did a quick search on these additional characters, but didn't find any
documentation.
It's never been a problem before, but since Microsoft actually mentions it
in help, perhaps the following...

Sub FixRange()
'// See Help on worksheet function "Clean"
Dim Rng As Range
Dim Cell As Range
Dim s As String

Set Rng = Selection
With WorksheetFunction
For Each Cell In Rng.Cells
s = .Clean(Cell.Value)
s = Replace(s, Chr(160), vbNullString) 'NonBreaking Space
s = Replace(s, Chr(127), vbNullString) '?
s = Replace(s, Chr(129), vbNullString) '?
s = Replace(s, Chr(141), vbNullString) '?
s = Replace(s, Chr(143), vbNullString) '?
s = Replace(s, Chr(144), vbNullString) '?
s = Replace(s, Chr(157), vbNullString) '?
Cell = Trim(s)
Next Cell
End With
End Sub

--
HTH :)
Dana DeLouis
Windows XP & Excel 2007