Dave Peterson wrote:
Looping through up to 65536 cells for each column seems like it would
take a long time.

The macro completed the job in a few seconds.


I'd use the equivalent of selecting the two columns and then
Edit|Replace.

You may want to post the code that failed. (My original code seems
to work ok for me.)


It was the cost as posted. Don't worry about it though as the code I used
works ok

regards

Brett


"Brett..." wrote:

Yes, I couldn't get it to work, which is why I used the code below.
Why is looping through each cell a problem? If I have unwanted codes
in a number of cells and two columns how else am I going to deal
with it?

cheers

Brett

Dave Peterson wrote:
You could loop through each cell (yech!) or just do the equivalent
of edit|replace in code.

Did you try the suggested code in the other post?



"Brett..." wrote:

Thanks Dave
The utility told me that the unwanted characters were CHR(13) but I
couldn't get your macro below to work.
Another problem was that the cells to be searched were not in the
first sheet or in column 1.
I eventually got round the problem by cutting and pasting the
relevant columns one at a time into Column 1 of the first sheet of
a workbook and using the following macro:

Sub ExcludeReturn()
Dim lngRows As Long, strCell As String
For lngRows = 1 To 65500
strCell = Sheet1.Cells(lngRows, 1)
If Len(Sheet1.Cells(lngRows, 1)) 0 Then
If (InStr(1, Sheet1.Cells(lngRows, 1), vbCrLf, vbTextCompare) 0)
Or _ (InStr(1, Sheet1.Cells(lngRows, 1), vbCr, vbTextCompare) 0)
Or _ (InStr(1, Sheet1.Cells(lngRows, 1), vbLf, vbTextCompare) 0)
Then Sheet1.Cells(lngRows, 1) = Replace(Sheet1.Cells(lngRows, 1),
vbCrLf, "") Sheet1.Cells(lngRows, 1) =
Replace(Sheet1.Cells(lngRows, 1), vbLf, "") Sheet1.Cells(lngRows,
1) = Replace(Sheet1.Cells(lngRows, 1), vbCr, "")
End If
End If
Next lngRows
MsgBox "done"
End Sub

Brett

Dave Peterson wrote:
Chip Pearson has a very nice addin that will help determine what
each character is:
http://www.cpearson.com/excel/CellView.htm

Option Explicit
Sub cleanEmUp()

Dim myBadChars As Variant
Dim myGoodChars As Variant
Dim iCtr As Long

myBadChars = Array(Chr(10), Chr(13)) '<--What showed up in
CellView?

myGoodChars = Array(" "," ") '<--the new characters--I
used a space?

If UBound(myGoodChars) < UBound(myBadChars) Then
MsgBox "Design error!"
Exit Sub
End If

For iCtr = LBound(myBadChars) To UBound(myBadChars)
ActiveSheet.Cells.Replace What:=myBadChars(iCtr), _
Replacement:=myGoodChars(iCtr), _
LookAt:=xlPart, SearchOrder:=xlByRows, _
MatchCase:=False
Next iCtr

End Sub

If you're new to macros, you may want to read David McRitchie's
intro at: http://www.mvps.org/dmcritchie/excel/getstarted.htm


"Brett..." wrote:

I've an excel file that contains lots of top bit characters.
Ive found that I can replace linefeed chars using the replace
tool and entering ALT 0010 but I'm still left with lots of
unknown top bit characters - shown in Excel and Notepad as a
rectangle in Times New Roman. Ive tried macros that remove CR's
and LF's but I'm still left with the top bit chars!
A sample can be found he
http://fp.catshill.plus.com/excel.zip

On a separate but related topic, is there a way to find the ASCII
code from a top bit character?

--

Brett

--

Brett

--

Brett