有没有办法找到 Excel 工作簿中所有工作表中都存在的值？

Question

我有一个包含 60 个选项卡的 Excel 工作簿。我需要删除所有选项卡中存在的任何值。是否有函数或某种方法可以做到这一点？

Answer 1

有几个选项；一个选项是使用公式查看该值是否存在于所有工作表中，然后如果存在则手动使用查找/替换将其删除。我不喜欢这个选项。

我的建议是使用带有 For Each 循环的宏来循环遍历文件中的所有工作表并检查是否找到了该值；如果在所有工作表中都找到了该值，则清除这些单元格。我们将执行以下操作：

Sub ClearAll()
    Const csValue As String = "Test"    'This is the value we're looking for


    Dim rngFound() As Range 'This is a list of all the cells containing the value
    Dim i As Integer    'This is a counter for the list
    Dim shtSheet As Worksheet   'This is an individual sheet
    Dim bolAll As Boolean   'This is a flag to indicate whether the value is in all sheets


    ReDim rngFound(1 To ActiveWorkbook.Sheets.Count)    'Size the list to the number of sheets
    bolAll = True   'Assume we will find the value in all sheets
    For Each shtSheet In ActiveWorkbook.Sheets  'Loop through each sheet
        Set rngFound(shtSheet.Index) = shtSheet.Cells.Find(what:=csValue) 'Look for the value in this worksheet
        If rngFound(shtSheet.Index) Is Nothing Then 'Check whether a match was found
            bolAll = False  'If a match wasn't found, change the flag and exit the loop
            Exit For
        End If
    Next shtSheet

    If bolAll Then  'If the value was on all sheets
        For i = 1 To UBound(rngFound)   'Loop through each flagged cell...
            rngFound(i).ClearContents   '...and delete its contents
        Next i
    End If
End Sub

要使用此代码，请通过按键盘上的 Alt+F11 或转到功能区上的“开发人员”选项卡并选择“Visual Basic”来打开 Visual Basic 编辑器。在 Visual Basic 编辑器的左侧窗格中，右键单击工作表的条目，然后选择“插入”->“模块：

复制上述代码并将其粘贴到新模块窗口中。您现在可以从宏界面访问并运行它。您需要将此代码第二行上的“测试”替换为您要查找的任何值。我们还有其他更改该值的方法，如果您需要使用多个值执行此操作 - 请告诉我您的需求。