The Code below works to clean non-printing chars on the activesheet.
DR - 4GUnwired.com

'Macro to Clean special characters out of Excel Spreadsheets
Option Explicit

Public Sub CleanBeforeExportToText()
Dim NumRows As Long
Dim NumCols As Long
Dim i As Long
Dim j As Long
Dim strCell As String
Dim c As Range

ActiveSheet.Cells.SpecialCells(xlCellTypeLastCell) .Activate
NumRows = ActiveCell.Row
NumCols = ActiveCell.Column

For i = 1 To NumCols
For j = 1 To NumRows
'Cells(j, i).Activate 'Comment this out for speed
Set c = Cells(j, i)
strCell = Cells(j, i).Value
c.Value = Application.WorksheetFunction.Clean(strCell)
Next
Next

End Sub

Won't clean html nbsp 160 in my testing.


Gord Dibben MS Excel MVP

On Thu, 8 Oct 2009 09:06:50 -0700 (PDT), DR
wrote:

The Code below works to clean non-printing chars on the activesheet.
DR - 4GUnwired.com

'Macro to Clean special characters out of Excel Spreadsheets
Option Explicit

Public Sub CleanBeforeExportToText()
Dim NumRows As Long
Dim NumCols As Long
Dim i As Long
Dim j As Long
Dim strCell As String
Dim c As Range

ActiveSheet.Cells.SpecialCells(xlCellTypeLastCell) .Activate
NumRows = ActiveCell.Row
NumCols = ActiveCell.Column

For i = 1 To NumCols
For j = 1 To NumRows
'Cells(j, i).Activate 'Comment this out for speed
Set c = Cells(j, i)
strCell = Cells(j, i).Value
c.Value = Application.WorksheetFunction.Clean(strCell)
Next
Next

End Sub

And it changes the active cell, and loops through the worksheet,
potentially creating hundreds of Range objects.

--JP


On Oct 8, 12:34*pm, Gord Dibben <gorddibbATshawDOTca wrote:
Won't clean html *nbsp *160 *in my testing.

Gord Dibben *MS Excel MVP

On Thu, 8 Oct 2009 09:06:50 -0700 (PDT), DR
wrote:



The Code below works to clean non-printing chars on the activesheet.
DR - 4GUnwired.com

It will also destroy any formulas in it path as well.

--
Rick (MVP - Excel)


"JP" wrote in message
...
And it changes the active cell, and loops through the worksheet,
potentially creating hundreds of Range objects.

--JP


On Oct 8, 12:34 pm, Gord Dibben <gorddibbATshawDOTca wrote:
Won't clean html nbsp 160 in my testing.

Gord Dibben MS Excel MVP

On Thu, 8 Oct 2009 09:06:50 -0700 (PDT), DR
wrote:



The Code below works to clean non-printing chars on the activesheet.
DR - 4GUnwired.com