Multi Sheet Macro, Compile Error

[lonewolf9899]

Hello,

I have found and tweaked a macro but can't quite work out all the bugs.

I have a spreadsheet with multiple sheets ("10", "20", "30", "40","50","70","90"). Each sheet has the same info in it. What I want to do is DELETE all rows in a sheet that do NOT match the Tab name (example; in the sheet named "10", I want to delete all rows that have the value "20", "30", etc in column A, so only "10" rows are in the "10" tab).

The macro below works great...on the first tab. So the "10" tab is all set, I move to the "20" tab. I tweak the macro so only "20" rows will stay, but when I run the macro, I get the error message :

COMPILE ERROR, Duplicate Declaration in Current Scope. The "Dim rng As Range, cell As Range, del As Range" is highlighted.

How Can I fix the macro so each sheet deletes the appropriate rows?





Sub Macro1()
' This macro deletes all rows on the active worksheet
' that have certain criteria
Dim rng As Range, cell As Range, del As Range
Set rng = Intersect(Range("A:A"), ActiveSheet.UsedRange)
For Each cell In rng
If (cell.Value) = "0" _
Or (cell.Value) = "20" _
Or (cell.Value) = "30" _
Or (cell.Value) = "40" _
Or (cell.Value) = "50" _
Or (cell.Value) = "70" _
Or (cell.Value) = "90" Then
If del Is Nothing Then
Set del = cell
Else: Set del = Union(del, cell)
End If
End If
Next cell
On Error Resume Next
del.EntireRow.Delete
End Sub

 

[njimack]

That error message usually indicates you've declared the same variable twice. I'd go with the below code, which will run on all worksheets, so no need for tweaking it based on which sheet is active...

Sub Test()
Dim ws As Worksheet
Dim r As Range
Dim Last_Row As Long
Dim i As Long

Application.ScreenUpdating = False

For Each ws In ThisWorkbook.Worksheets

    Select Case ws.Name
    Case 0, 10, 20, 30, 40, 50, 70, 90
    
    Last_Row = ws.Range("A" & Rows.Count).End(xlUp).Row
    
    For i = Last_Row To 2 Step -1
        If ws.Cells(i, 1).Value = ws.Name Then ws.Cells(i, 1).EntireRow.Delete
    Next i
    
    End Select

Next ws

Application.ScreenUpdating = True

End Sub

 

[JackDanIce]

Alternative to njimack's suggestion, you can try:

Sub DeleteRowsBasedonName ()
Dim i as Long
Application.DisplayAlerts = False
For i = 1 to Worksheets.Count
  If Sheets(i).Autofiltermode Then Sheets(i).Autofiltermode = False
  Sheets(i).Range("A1").AutoFilter Field:=1, Criteria1:<>Sheets(i).Name
  ActiveSheet.UsedRange.Resize(ActiveSheet.UsedRange.Rows.Count - 1).Rows.Delete
Next i
Application.DisplayAlerts = True
End Sub

(I think njimack's code is deleting rows when it does match the name of the worksheet but the request was to delete all rows that does not match the worksheet name; apologies if I've misread your code njimack)