有时候,数据中包含着一些字符,这些字符会使某些数据无法使用。下面是excelbianalytics.com中提供的一个宏,按下列方式清理工作表:3.删除ASCII码为127、129、141、143、144、157、160的字符。Sub CleanUpData() Dim ws As Worksheet Dim rng As Range Dim cell As Range Dim ArrCodes Dim i As Long Set ws = ActiveSheet On Error Resume Next Set rng = ws.UsedRange.SpecialCells(xlConstants, xlNumbers + xlTextValues) If rng Is Nothing Then Exit Sub End If On Error GoTo 0 ArrCodes = Array(127, 129, 141, 143, 144, 157, 160) Application.ScreenUpdating = False Application.Calculation = xlCalculationManual For Each cell In rng '使用CLEAN函数删除32个不可打印字符(0至31) 'Trim删除前导和尾随空格 cell = Trim(WorksheetFunction.Clean(cell)) '删除代码为127, 129, 141, 143, 144, 157, 160字符 For i = LBound(ArrCodes) To UBound(ArrCodes) cell = Replace(cell, Chr(ArrCodes(i)), '') Next i Next cell Application.ScreenUpdating = True Application.Calculation = xlCalculationAutomatic End Sub 初学者提示:按Alt+F11打开VBE编辑器,插入一个标准模块,将上述代码复制该模块中,然后按F5键运行。
|