Excel VBA宏：定位列中第一个空单元格并自动填充

Question

我有一个电子表格，其中有两列：A列和B列。

A列包含从查询中提取的姓名（例如Brian、Bob、Bill等），而B列包含三种状态之一（已指定、进行中或挂起）。

但是，这个查询有时会显示状态为“已指定”的某些行项目，而相应表示A列中的名称的单元格为空。因此，我手动填充这些空单元格，以填入“未知”一词。

现在，我想创建一个宏，查找A列中每个空单元格，并在其右侧的单元格包含“已指定”一词时填充单词“未知”。

因此，条件如下：

1. A列中的单元格为空； 2. 相应的右侧单元格（B列）包含单词“已指定”。

以下是我的代码：

Private Sub CommandButton2_Click()

    For Each cell In Columns("A")
        If ActiveCell.Value = Empty And ActiveCell.Offset(0, 1).Value = "Assigned" Then ActiveCell.Value = "Unknown"
    Next cell

End Sub   

- user1663562

Answer 1

这里不需要循环，可以利用Excel内置的方法来加快执行速度。

Private Sub CommandButton2_Click()

    Application.ScreenUpdating = False

    With ActiveSheet.UsedRange
        .AutoFilter Field:=1, Criteria1:=""
        .AutoFilter Field:=2, Criteria1:="Assigned"

        If WorksheetFunction.CountBlank(.Columns(1)) > 0 Then
            If .Columns(1).SpecialCells(xlCellTypeVisible).Count > 1 Then
                .Columns(1).SpecialCells(xlCellTypeBlanks).Value = "Unknown"
            End If
        End If

        .AutoFilter
    End With

    Application.ScreenUpdating = True

End Sub

- Reafidy

---

+1 -- 这确实比我用循环发布的版本快了一点。我在一个有超过一百万行的表格中计时了两个版本。这段代码在我的笔记本电脑上运行了2秒；我的循环运行了5秒。在几千行的范围内，它们都在一秒钟以内运行，我无法看出区别。不过我注意到了一个问题——如果第一列没有空白，它会崩溃。 - Jon Crowell

非常好！我认为将其放入代码是值得的，毕竟，用户可能会点击两次按钮。我也很感谢学习您的技术。多年来，我已经从 Recorder 转变为 Selector，再到 Iterator，现在正朝着下一个水平——Autofilterer 的方向前进！ - Jon Crowell

你有向Sid提出那个想法吗？他的回答经常是完整的代码块，带着截图和详尽的博客式解释。我并不反对你的方法，但我认为说“在Stack Overflow上的想法”太笼统了。 - Jon Crowell

然而，应修改代码以满足用户的要求，即仅在usedrange中填充空白单元格为"unknown"，而不是整列。 - Scott Holtzman

@Reafidy - 啊，是的，现在我明白了，它只是填充列A中使用范围内的空单元格。我想我昨天看错了 :) - Scott Holtzman

Answer 2

欢迎来到SO。

尝试这段代码。它会更快地运行并且应该能够得到你想要的结果。

更新：使代码更加健壮！

Private Sub CommandButton2_Click()

Dim cel As Range, rngFind As Range, rngFilter As Range
Dim wks As Worksheet

Set wks = Sheets("sheet1")

With wks

    '-> Error check to make sure "blanks" exist
    Set rngFind = .Range("A1:A" & .Range("B" & Rows.Count).End(xlUp).Row).Find("", lookat:=xlWhole)

    If Not rngFind Is Nothing Then

        Set rngFilter = .Range("A1:B" & .Range("B" & Rows.Count).End(xlUp).Row)

        rngFilter.AutoFilter 1, "="

        '-> Error check to make sure "assigned" exists for blank cells
        Set rngFind = .Columns("B:B").SpecialCells(xlCellTypeVisible).Find("Assigned", lookat:=xlWhole)

        If Not rngFind Is Nothing Then
        '-> okay, it exists. filter and loop through cells

            rngFilter.AutoFilter 2, "Assigned"

            Set rngFind = Intersect(.UsedRange, .UsedRange.Offset(1), .Columns(1)).SpecialCells(xlCellTypeVisible)

            For Each cel In rngFind

                If cel.Offset(0, 1).Value = "Assigned" Then cel.Value = "Unknown"

            Next cel

        End If

    End If

End With


End Sub

- Scott Holtzman

---

我在使用.Text时遇到了“对象必需”错误，但是使用.Value却可以正常工作。可能是我的Excel版本问题，也可能是代码问题。 - LittleBobbyTables - Au Revoir

@LittleBobbyTables：没有.Text属性，它是只读的。你必须使用.Value。 - Siddharth Rout

@LittleBobbyTables / @SiddharthRout：关于.Text和.Value的问题，我的错误。感谢你们的敏锐眼力。 - Scott Holtzman

@ScottHoltzman：不，那不是双重过滤器。我的意思是这样的。例如：ActiveSheet.Range("$A$1:$B$9").AutoFilter Field:=1, Criteria1:="=" ActiveSheet.Range("$A$1:$B$9").AutoFilter Field:=2, Criteria1:="<>" - Siddharth Rout

空单元格位于列（A）中，例如单元格（a2）。值= bob和单元格（b2）。值=已分配，单元格（a3）。值=空，单元格（b3）。值=已分配。我想找到所有列A中具有空值的单元格，并且其旁边的单元格具有“已分配”一词（例如单元格（a3）和（a4））。 - user1663562

Answer 3

如果你只需要做几次，那么你可以：

1. 将使用的范围格式化为表格
2. 在A列上筛选，仅显示“(空白)”
3. 在B列上筛选，仅显示“已分配”
4. 选择B列中所有结果单元格
5. 按下alt + : 来仅选择可见单元格
6. 按下F2
7. 输入“未知”
8. 按下ctrl + enter

你的坏数据应该变好了！

显然，这是非VBA解决方案，但如果您可以避免编码，最好不要使用它。

- Brad

---

我通常会对列表进行排序，向下滚动，然后将单词“Unknown”插入到空白项中。我创建了一个宏，从另一个工作表中提取相关数据并进行排序。现在我只需要创建第二个宏，这个过程就可以在两次点击中完成。我应该如何在这个表格上发布Excel表格？还是直接发送给您？ - user1663562

@user1663562 - 所以你正在处理的数据已经是由你动态组装的了？并且将来也需要动态组装（不是一次性操作）？如果确实需要这样做，那么你应该采用Scott的答案。如果你不想涉及VBA，我的方法只是一个快速解决方案。 - Brad

@user1663562 - 另外，我认为在SO上发布完整的文件并不可取（或者就我所知道的而言确实不可能）。这样做可能会解决你的问题，但它会将解决方案从公共领域中移除。未来的人们将无法像你一样受益。 - Brad

@user1663562 - 你可以使用www.dropbox.com作为文件上传位置。然后你可以在SO上发布链接。 - Scott Holtzman

这是一个链接，指向一个模拟实际电子表格的虚拟文件 http://dl.dropbox.com/u/104835256/Dummy.xls - user1663562