vba 如何将 CLEAN() 公式应用于整个 Excel 工作表？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/25240512/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to apply CLEAN() formula to entire Excel sheet?
提问by Madhu Sudhan Reddy
I have an Excel file with almost 50 columns and 10,000 rows. These contain non-printable characters in some cells; I want to remove these characters from all cells in the sheet.
我有一个包含近 50 列和 10,000 行的 Excel 文件。这些在某些单元格中包含不可打印的字符；我想从工作表中的所有单元格中删除这些字符。
It would be difficult to apply the CLEAN()function to each and every column. Is there an alternative?
将CLEAN()函数应用于每一列是很困难的。有替代方案吗？
回答by Siddharth Rout
I can't see it visually.. If you see that character at the end of the cell text, then you can find that value using the below code
我在视觉上看不到它。如果你在单元格文本的末尾看到那个字符，那么你可以使用下面的代码找到那个值
debug.Print asc(right(range("D1").Value,1))
Simply replace 110 with that value in the code below.
只需在下面的代码中用该值替换 110。
Sub Sample()
Dim ws As Worksheet
'~~> Set this to the relevant worksheet
Set ws = ThisWorkbook.Sheets("Sheet1")
ws.Cells.Replace What:=Chr(110), Replacement:="", LookAt:=xlPart, SearchOrder _
:=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
End Sub
HEREis the ASCII table for your reference.
这里是 ASCII 表供您参考。
回答by z??
To apply the excel CLEANfunction on each cell of the first 50 columns and 10000 rows:
要CLEAN在前 50 列和 10000 行的每个单元格上应用 excel函数：
Sub cleanSheet()
Dim r As Range
Application.ScreenUpdating = False
For Each r In Range(Cells(1, 1), Cells(10000, 50))
r = WorksheetFunction.Clean(r)
Next r
Application.ScreenUpdating = True
End Sub
回答by Isha
You can simply create another sheet then, in the first cell, write the clean formula =Clean('sheet1'!A1)and enter.
您可以简单地创建另一个工作表，然后在第一个单元格中，编写干净的公式=Clean('sheet1'!A1)并输入。
Now drag the columns and rows. You will get your cleaned up data in this sheet. You can also use the trim function in the same way.
现在拖动列和行。您将在此工作表中获得清理过的数据。您也可以以相同的方式使用修剪功能。
Hope this helps !
希望这可以帮助 ！
回答by Rosalinda Albrecht
Thank you z??! This was just what I was looking for!
谢谢z？？！这正是我要找的！
I noticed in the comments there was some call to reference only non-empty cells. I used this instead of the cell references and it vastly improved the processing time.
我注意到在评论中有一些只引用非空单元格的调用。我用它代替了单元格引用，它大大缩短了处理时间。
For Each r In ActiveSheet.UsedRange
Hopefully this helps a little. (Though I'm pretty late to the party.)
希望这会有所帮助。（虽然我参加派对已经很晚了。）
