Macro for deleting non-printable characters from EXCEL
 

I Have written the function below to remove some non printable characters
from my WORKBOOK.
However when I run the code it delete all entries from my worksheets.

Sub Clean()
Dim j As Integer
Dim vAddText

Application.DisplayAlerts = False
Application.StatusBar = "Starting cleaning of workbook " &
Application.ActiveWorkbook.FullName
On Error GoTo 0
vAddText = Array(Chr(129), Chr(141), Chr(143), Chr(144), Chr(157))
For J = 1 To 31
If J < 10 Then Cells.Replace What:=Chr(J), Replacement:="",
LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Next
For J = 0 To UBound(vAddText)
Cells.Replace What:=vAddText(J), Replacement:="", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Next
Application.StatusBar = "Ending cleaning of workbook " &
Application.ActiveWorkbook.FullName
Application.DisplayAlerts = True
End Sub

--
George, Southampton, UK

Macro for deleting non-printable characters from EXCEL
 

This is a little longer:

Sub clean()

Dim vAddText(0 To 34) As String

vAddText(0) = Chr(1)
vAddText(1) = Chr(2)
vAddText(2) = Chr(3)
vAddText(3) = Chr(4)
vAddText(4) = Chr(5)
vAddText(5) = Chr(6)
vAddText(6) = Chr(7)
vAddText(7) = Chr(8)
vAddText(8) = Chr(9)
vAddText(9) = Chr(11)
vAddText(10) = Chr(12)
vAddText(11) = Chr(13)
vAddText(12) = Chr(14)
vAddText(13) = Chr(15)
vAddText(14) = Chr(16)
vAddText(15) = Chr(17)
vAddText(16) = Chr(18)
vAddText(17) = Chr(19)
vAddText(18) = Chr(20)
vAddText(19) = Chr(21)
vAddText(20) = Chr(22)
vAddText(21) = Chr(23)
vAddText(22) = Chr(24)
vAddText(23) = Chr(25)
vAddText(24) = Chr(26)
vAddText(25) = Chr(27)
vAddText(26) = Chr(28)
vAddText(27) = Chr(29)
vAddText(28) = Chr(30)
vAddText(29) = Chr(31)
vAddText(30) = Chr(129)
vAddText(31) = Chr(141)
vAddText(32) = Chr(143)
vAddText(33) = Chr(144)
vAddText(34) = Chr(157)

For Each r In ActiveSheet.UsedRange
v = r.Value
For j = 0 To 34
v = Replace(v, vAddText(j), "")
Next
r.Value = v
Next
End Sub

but it should work.
--
Gary''s Student - gsnu200820


"George Kibuuka" wrote:

I Have written the function below to remove some non printable characters
from my WORKBOOK.
However when I run the code it delete all entries from my worksheets.

Sub Clean()
Dim j As Integer
Dim vAddText

Application.DisplayAlerts = False
Application.StatusBar = "Starting cleaning of workbook " &
Application.ActiveWorkbook.FullName
On Error GoTo 0
vAddText = Array(Chr(129), Chr(141), Chr(143), Chr(144), Chr(157))
For J = 1 To 31
If J < 10 Then Cells.Replace What:=Chr(J), Replacement:="",
LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Next
For J = 0 To UBound(vAddText)
Cells.Replace What:=vAddText(J), Replacement:="", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Next
Application.StatusBar = "Ending cleaning of workbook " &
Application.ActiveWorkbook.FullName
Application.DisplayAlerts = True
End Sub

--
George, Southampton, UK