用0替换空值

Question

我希望检查 D 到 O 列中的所有单元格。如果单元格为空，则用一个硬零替换它。

我有以下代码：

Sub replace()
Dim rng As Range, cell As Range
Dim aantalrijen As Long

    With Worksheets("Schaduwblad")
        aantalrijen = .Range("A1", .Range("A1").End(xlDown)).Cells.Count - 1
        Set rng = .Range(.Cells(2, "D"), .Cells(aantalrijen, "O"))

        For Each cell In rng
            cell = WorksheetFunction.Substitute(cell, "", "0")
        Next
    End With
End Sub

这段代码在处理过程中卡住了。唯一的选择是通过按下Escape键来结束程序。

- DutchArjo

---

你不能使用查找和替换功能来避免循环吗？ - SJR

不行，因为数据在一个隐藏的工作表上，并且这是每4周返回一次的过程。 - DutchArjo

Answer 1

您不需要循环遍历所有单元格。使用 .SpecialCells 让 Excel 找到空单元格：

On Error Resume Next
rng.SpecialCells(xlCellTypeBlanks, xlCellTypeConstants).Value = 0
On Error GoTo 0

如果没有找到空单元格，就需要使用错误陷阱。

---

所以您的整个例程可以被替换为：

Sub replace()

    On Error Resume Next

      With Worksheets("Schaduwblad")
            .Range(.Cells(2, "D"), .Cells(.Cells(.Rows.Count, 1).End(xlUp).Row, "O")) _
                .SpecialCells(xlCellTypeBlanks, xlCellTypeConstants).Value = 0
      End With

    On Error GoTo 0

End Sub

---

以下是与您下方评论相关的代码版本，但它是按行填充的。为了测试这个版本，我创建了一个227,000 x 15的数据块，然后使用随机数生成器在其中打了100,000个洞，清空了那些单元格。然后我运行了下面的代码，用了33秒钟把那100,000个洞填回去。

Sub replace()

Dim rangesection As Range

    On Error Resume Next

      With Worksheets("Schaduwblad")
        For Each rangesection In .Range(.Cells(2, "D"), .Cells(.Cells(.Rows.Count, 1).End(xlUp).Row, "O")).Rows
            rangesection.SpecialCells(xlCellTypeBlanks, xlCellTypeConstants).Value = 0
        Next
      End With

    On Error GoTo 0

End Sub

- CLR

---

对于我的大多数模板，代码都能完美运行。然而，我现在遇到了另一个问题，即在例程完成之前需要花费很长时间。我想知道为什么。有问题的模板共有227850行，而一个正常工作的模板只有87360行。因此，有问题的模板需要检查的行数约为正常模板的3倍，但运行时间却要多得多（我已经等待了至少15分钟，然后取消了执行）。 - DutchArjo

我刚刚在一个227,000 x 15的单元格表上使用了上述代码，其中大多数是空白的，它只花费不到2秒钟的时间将它们全部更改为0。 - CLR

如果您选择的答案更快，请告诉我，因为我想了解原因。 - CLR

延迟的原因是由于建立空单元格范围。使这个过程更快的唯一方法是了解空单元格的性质。它们是否可能独立存在，还是更可能与同一行或列中的其他空白一起出现。它们是否在大的连续块中，还是分散在单个位置。基于它们分散在单个位置的假设（最坏情况），以及一个大约有10-15个单元格宽度的表格，我建议逐行运行宏向下表格。我会补充我的答案。 - CLR

它确实有影响，但我认为可能与单元格的格式有关。这些单元格是空的，但我看到它们有格式。这造成了相当大的差异，尽管我不知道确切的原因。 - DutchArjo

毋庸置疑，确保关闭计算。基于更改的计算（以及基于计算的条件格式）显然会减慢速度。您尝试过逐行代码吗？ - CLR

Answer 2

我从未使用过替换方法.. 我会通过使用 IsEmpty() 函数检查单元格是否为空来完成此操作。

因此，您可以将

cell = WorksheetFunction.Substitute(cell, "", "0")

替换为

If IsEmpty(cell) Then cell.value = 0

完整代码：

Sub replace()
Dim rng As Range, cell As Range
Dim aantalrijen As Long

  With Application.ThisWorkbook.Worksheets("Schaduwblad")
        aantalrijen = .Range("A1", .Range("A1").End(xlDown)).Cells.Count - 1
        Set rng = .Range(.Cells(2, "D"), .Cells(aantalrijen, "O"))


    For Each cell In rng
        If IsEmpty(cell) Then cell.value = 0
    Next
  End With
End Sub

- T. Nesset

---

你的代码帮助我在最大的数据文件中将空单元格转换为0，而且速度非常快。 - DutchArjo

@DutchArjo 很高兴能帮到你，如果你在开头没有使用 Application.ScreenUpdating = False，然后在结尾使用 Application.ScreenUpdating = True，你可能会让它运行得更快。 - T. Nesset