清除所有没有颜色的单元格

Question

我有一个大表(100列*1000行(，这个表中的某些单元格填充了不同的颜色(红色、黄色、蓝色、橙色和其他颜色(。我想清除所有选定的没有颜色的单元格。我写了一个简单的宏，它适用于小表，但对于大表，计算能力会下降，如果在同一范围内选择了几个彩色单元格，则这些单元格不会被清除。当指定区域有不同颜色时，我如何减少桌子的清洁时间并修复错误？

Sub ClearData()
For Each cell in Selection
If cell.Interior.ColorIndex = xlNone Then
cell.ClearContents
End If
Next cell
MsgBox "Done!"
End Subs

Answer 1

Sub ClearData()
Dim Cell As Range
Dim URng As Range
For Each Cell In Selection
If Cell.Interior.ColorIndex = xlNone Then GoSub UnionRange
Next
If Not URng Is Nothing Then URng.ClearContents: MsgBox "Done!"
Exit Sub
UnionRange:
If Not URng Is Nothing Then
Set URng = Union(URng, Cell)
Else
Set URng = Cell
End If
Return
End Sub

Answer 2

试试这个代码

Sub ClearData()
Dim c As Range, r As Range
Application.ScreenUpdating = False
With ThisWorkbook.Worksheets("Sheet1")
For Each c In .Range("A1").CurrentRegion
If c.Interior.ColorIndex = xlNone Then
If r Is Nothing Then Set r = c Else Set r = Union(r, c)
End If
Next c
If Not r Is Nothing Then r.Cells.Clear
End With
Application.ScreenUpdating = True
MsgBox "Done...", 64
End Sub