Unable to Loop Multiple Worksheets

[jackc]

I have a macro that deletes unwanted rows from a worksheet. The macro
works fine when applied to each worksheet individually, when I attempt
to loop in all worksheets it only deletes the rows in the first
worksheet.

Sub Delete_Rows()
' This macro deletes specified rows on the active worksheet

Dim rng As Range, cell As Range, del As Range

For Each ws In ActiveWorkbook.Worksheets
ws.Activate
If ws.Name <> "Summary" And ws.Name <> "Reference" Then
Set rng = Intersect(Range("C6:C600"), ActiveSheet.UsedRange)
For Each cell In rng
If (cell.Value) = "USD" _
Or (cell.Value) = "USD Total" _
Or (cell.Value) = "KEY" _
Or (cell.Value) = "EUR" Then
If del Is Nothing Then
Set del = cell
Else: Set del = Union(del, cell)
End If
End If
Next
On Error Resume Next
del.EntireRow.Delete
End If
Next
End Sub

Does anyone have a solution.

 

[JAVB]

I copied your code to a new workbook and inserted USD in random cells o
three sheets. Your code went to all three sheets and removed the USD.
Is your code actually going to all the sheets you need it to go to o
is there some other problem. From the wording of your problem I a
assuming that the For statement is not activating the other sheets. I
this correct?
JAV

 

[jackc]

The macro seems to be looping thru each of the sheets. However, it is
only deleting the rows in the first sheet.

When you say you entered USD in random cells, I assume they were in
column "C" after row 5.

 

[Dave Peterson]

A range belongs to a worksheet. When you loop through the first worksheet, you
build that del range up with cells you need.

When you go to the next worksheet, del is never re-initialized to nothing. And
since del has been deleted, it's kind of in a wierd situation. It isn't an
existing range--it's kind of in a netherworld (my word--not anyone elses!)

You can just make sure that del is reset to Nothing each time you start
processing a different worksheet:

For Each ws In ActiveWorkbook.Worksheets
ws.Activate
set del = nothing

 

[jackc]

Dave,

Thanks a lot, I'm sure this will work

Jack C