用空白替换包含零的单元格 [英] Replace cells containing zero with blank
问题描述
我有大量的数据A4:EW8000 +,我想用空白单元格替换包含零的单元格.格式化单元格不是一种选择,因为我需要保留当前格式.我正在寻找用空白单元格替换零的最快方法.
I have a very large amount of data A4:EW8000+ that I want to replace cells containing a zero with a blank cell. Formatting the cells is not an option as I need to retain the current format. I'm looking for the fastest way to replace zeros with blank cells.
我可以通过循环来做到这一点,但是它非常慢.下面的代码:
I can do this with looping but its very slow. Below code:
Sub clearzero()
Dim rng As Range
For Each rng In Range("A1:EW10000")
If rng.Value = 0 Then
rng.Value = ""
End If
Next
End Sub
有没有简单的方法可以做到这一点而无需循环?
Is there an easy way I can do this without looping?
我尝试了以下代码,但似乎无法正常工作.它将Excel挂起一会儿(无响应),然后循环遍历该范围并空白每个单元格.
I tried the below code, but it doesn't seem to work correctly. It hangs Excel for a while (not responding) then it loops through the range and blanks every cell.
Sub RemoveZero()
Dim LastRow As Long
Const StartRow As Long = 2
LastRow = Cells.Find(What:="0", SearchOrder:=xlRows, SearchDirection:=xlPrevious, LookIn:=xlValues).Row
With Range("B:EW")
.Value = Range("B:EW").Value
.Replace "0", "0", xlWhole, , False
On Error Resume Next
.SpecialCells(xlConstants).Value = ""
.SpecialCells(xlFormulas).Value = 0
End With
End Sub
推荐答案
这是自动执行替换所需的所有VBA:
This is all the VBA you need to automate the replacements:
[a4:ew10000].Replace 0, "", 1
.
更新
虽然以上内容很简洁,但以下内容可能是最快的方法.您的整个范围在我的计算机上花费的时间不到四分之一秒:
While the above is concise, the following is likely the fastest way possible. It takes less than a quarter of a second on my computer for your entire range:
Sub RemoveZero()
Dim i&, j&, v, r As Range
Set r = [a4:ew10000]
v = r.Value2
For i = 1 To UBound(v, 1)
For j = 1 To UBound(v, 2)
If Len(v(i, j)) Then
If v(i, j) = 0 Then r(i, j) = vbNullString
End If
Next
Next
End Sub
这篇关于用空白替换包含零的单元格的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!