Error on Union when looping through multiple worksheets

Question

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

in what line you get an error? I don't have any issues with your code — Dmitry Pavliv
Couple of things: 1) you should probably exclude worksheet All from the For loop search. 2) your description and code don't match: do you want to delete the found matches from worksheet All or the other sheets? — chris neilsen
Okay, so how do I search the 'All' sheet if I don't include it in the 'For' loop? Also, I want to delete the found matches from worksheet 'All'. Not the other worksheets. — kzs

kzs kzs · Accepted Answer · 2014-04-29T13:22:02

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)

Error on Union when looping through multiple worksheets

1 Answers