Excel VBA Sub Clean Sheet Questions. How much is necessary?

[joel009]

I am finding Chr(160) and Chr(157) in recent spreadsheets to format. I decided to set up a Clean sub to call to remove unwanted characters. At first I tried removing all non visible characters and cleared all the cells. I am narrowing down the offensive characters and was hoping someone would chime in on which are ok and which are not. I discovered as I removed chr(1) my cells would clear and have no idea why. I added in the Chr(8-10) and Chr(13) since they have given my problems in Access and VB and then I started wondering if they were necesary. In Access I was familiar with removing Chr(8-10) and Chr(13) but had not encountered characters above 127. The method of collecting the spreadsheet data has recently moved to capturing from a web page, not sure of that method either. Most likely right click/ save as.

What I have, just a start:
Public Function CleanWorksheet()
Dim LastRow, LastCol, FirstRow, FirstCol
Dim rngWSRange As Range 'Object

' Find the last real row
Set LastRow = ActiveSheet.Cells.Find(What:="*", _
SearchDirection:=xlPrevious, _
SearchOrder:=xlByRows)

' Find the last real column
Set LastCol = ActiveSheet.Cells.Find(What:="*", _
SearchDirection:=xlPrevious, _
SearchOrder:=xlByColumns)

' Find the first real row
Set FirstRow = ActiveSheet.Cells.Find(What:="*", _
SearchDirection:=xlNext, _
SearchOrder:=xlByRows)

' Find the first real column
Set FirstCol = ActiveSheet.Cells.Find(What:="*", _
SearchDirection:=xlNext, _
SearchOrder:=xlByColumns)

''' ActiveSheet.PageSetup.PrintArea = Range(Cells(FirstRow.row, FirstCol.column), Cells(LastRow.row, LastCol.column)).Address
Set rngWSRange = Range(Cells(FirstRow.row, FirstCol.column), Cells(LastRow.row, LastCol.column))

'Use Replace to remove Ascii Characters 0-31, char 127, 129, 141, 143, 144, and 157 change 160 to 39 so trim will work

rngWSRange.Select

With rngWSRange

clutter = Array(Chr$(0), Chr$(8), Chr$(9), Chr$(10), Chr$(13), Chr$(127), Chr$(141), Chr$(143), Chr$(144), Chr$(157))

For i = 0 To 161
Select Case i
Case 0, 8 - 10, 13, 127, 141, 143, 144, 157 ' 8, 9, 10, and 13
.Replace _
What:=Chr(i), Replacement:="", _
SearchOrder:=xlByColumns, _
MatchCase:=False, _
SearchFormat:=False, _
ReplaceFormat:=False
Case 160 'replace HTML space chr(160) with chr(32) so trim will remove
.Replace _
What:=Chr(i), Replacement:=Chr(32), _
SearchOrder:=xlByColumns, _
MatchCase:=False, _
SearchFormat:=False, _
ReplaceFormat:=False
Case Else
'do nothing
End Select
Next i

End With

Range("A1").Select

End Function

I know the Char 157 and 160 have already given me problems but what else may cause me problems later.
If this is a FAQ I could not find.

Joel

 

[joel009]

Corrected

Case 0, 8 - 10, 13, 127, 141, 143, 144, 157 ' 8, 9, 10, and 13
.Replace _
What:=clutter(i), Replacement:="", _
SearchOrder:=xlByColumns, _
MatchCase:=False, _
SearchFormat:=False, _
ReplaceFormat:=False

Joel

 

[PHV]

Why not simply yhis ?

clutter = Array(Chr$(0), Chr$(8), Chr$(9), Chr$(10), Chr$(13), Chr$(127), Chr$(141), Chr$(143), Chr$(144), Chr$(157))
With rngWSRange
    .Replace _
            What:=Chr(160), Replacement:=Chr(32), _
            SearchOrder:=xlByColumns, _
            MatchCase:=False, _
            SearchFormat:=False, _
            ReplaceFormat:=False
    For Each i In clutter
        .Replace _
                What:=i, Replacement:="", _
                SearchOrder:=xlByColumns, _
                MatchCase:=False, _
                SearchFormat:=False, _
                ReplaceFormat:=False
    Next
End With

FYI, Chr(160) isn't a HTML space but a Non Break Space (&nbsp

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886