循环遍历多个工作表时，联合出错(Error on Union when looping through multiple worksheets)

循环遍历多个工作表时，联合出错(Error on Union when looping through multiple worksheets)

我正在尝试删除一个工作表中与多个其他工作表中的行匹配条件的行。 我需要遍历工作簿中的所有其他工作表，每次找到匹配的东西时，我都会删除第一个工作表中的整行。 我正在联盟上获得错误1004。 我认为这可能是因为您不能在工作表之间使用Union，所以我在每张工作表后将其设置为Nothing。 我仍然得到同样的错误。

这是代码：

Sub findRemaining() Dim rngToDel As Range Dim fRng As Range 'Fund range Dim wCell As Range 'Working sheet cell Dim wRng As Range 'Working sheet range Dim WS_Count As Integer Dim I As Integer Set fRng = Worksheets("All").Range("B2:B1495") WS_Count = ActiveWorkbook.Worksheets.Count For I = 2 To WS_Count Set rngToDel = Nothing Set wRng = Worksheets(I).Range("B2:B200") For Each wCell In wRng 'Loop through all working cells ' if wCell found in Fund range then delete row If Not IsError(Application.Match(Trim(wCell.Value), fRng, 0)) Then If rngToDel Is Nothing Then Set rngToDel = wCell Else Set rngToDel = Union(rngToDel, wCell) End If End If Next wCell If Not rngToDel Is Nothing Then rngToDel.EntireRow.Delete Next I End Sub

I'm trying to delete rows in one sheet that match criteria from rows taken from multiple other worksheets. I need to cycle through all the other worksheets in my workbook, and each time I find something that matches, I delete the entire row in the first sheet. I'm getting Error 1004 on Union. I thought this might be caused because you can't use Union across sheets, so I set it to Nothing after each sheet. I'm still getting the same error.

Here's the code:

Sub findRemaining() Dim rngToDel As Range Dim fRng As Range 'Fund range Dim wCell As Range 'Working sheet cell Dim wRng As Range 'Working sheet range Dim WS_Count As Integer Dim I As Integer Set fRng = Worksheets("All").Range("B2:B1495") WS_Count = ActiveWorkbook.Worksheets.Count For I = 2 To WS_Count Set rngToDel = Nothing Set wRng = Worksheets(I).Range("B2:B200") For Each wCell In wRng 'Loop through all working cells ' if wCell found in Fund range then delete row If Not IsError(Application.Match(Trim(wCell.Value), fRng, 0)) Then If rngToDel Is Nothing Then Set rngToDel = wCell Else Set rngToDel = Union(rngToDel, wCell) End If End If Next wCell If Not rngToDel Is Nothing Then rngToDel.EntireRow.Delete Next I End Sub

最满意答案

问题是我在wRng中将范围设置为wCell删除，但实际上我想要删除的是fRng中的单元fRng 。 通过修复其余代码完美运行。 （谢谢克里斯）

The problem was I set range to delete as wCell in wRng, but really what I wanted to delete was cells in fRng. By fixing that the rest of the code worked perfectly. (thanks chris)