vba - 如何将选择中的所有空单元格转换为 NULL 值？

Question

我有一个 Selection可以是任何随机范围，例如("A1:O10")
我需要确保此范围内的所有空白单元格的值为 NULL .

我怎样才能做到这一点 ？

For Each c In Selection.Cells
        If Abs(c.Value) = "" Then c.Value = "NULL"
Next

Answer 1

编辑:被提议作为答案，但将其留在这里作为 不是 去做(基于 simoco 对 SpecialCells 和 UsedRange 的观察)

On Error Resume Next
Selection.SpecialCells(xlCellTypeBlanks).Value = "NULL"
On Error Goto 0

修复了 SpecialCells “otchas:

SpecialCells 仅适用于工作表的 UsedRange(即使您选择了该范围之外的单元格)

SpecialCells 不适用于单个单元格选择:在这种情况下它会自动扩展为整个工作表

假设单区域选择:

With Selection
    With .Cells(.Cells.Count)
        If Not .HasFormula And Len(.Value) = 0 Then
            .Value = "NULL"
        End If
    End With
    If .Cells.Count = 1 Then Exit Sub  
    On Error Resume Next
    .SpecialCells(xlCellTypeBlanks).Value = "NULL"
    On Error GoTo 0
End With

来自 Siddharth Rout:另一种不使用循环的方式

Sub Sample()
    With ActiveSheet
        '~~> How like is that the bottom right cell is filled????
        '~~> Excel 2007+ - XFD1048576
        '~~> Excel 2003 - IV65536
        .Cells(.Rows.Count, .Columns.Count) = "A"

        If Selection.Cells.Count < 2 Then
            If Selection.Value = "" Then Selection.Value = "NULL"
        Else
            On Error Resume Next
            Selection.SpecialCells(xlCellTypeBlanks).Value = "NULL"
            On Error GoTo 0
        End If
        .Cells(.Rows.Count, .Columns.Count).ClearContents
    End With
End Sub