我有一个包含 60 个选项卡的 Excel 工作簿。我需要删除所有选项卡中存在的任何值。是否有函数或某种方法可以做到这一点?
答案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 编辑器的左侧窗格中,右键单击工作表的条目,然后选择“插入”->“模块:
复制上述代码并将其粘贴到新模块窗口中。您现在可以从宏界面访问并运行它。您需要将此代码第二行上的“测试”替换为您要查找的任何值。我们还有其他更改该值的方法,如果您需要使用多个值执行此操作 - 请告诉我您的需求。