Repeating Process in New Sheet

[JordanW]

Hi, I just started working with variables and not sure I understand them. I have the following code that basically seaches a range and if the specified value doesn't appear, the row will be deleted. This works for the first sheet that is referenced but not for the second. Originally, I had defined the variables in both pieces and got an error about "duplicate declaration in current scope". I thought the correct thing to do was to move the dim statements to the top of the code and they only appear once but my code still does not work for the second sheet (although I don't get the error any longer). I'd appreciate some help. Thanks.

Dim rng As Range, cell As Range, del As Range
  Dim strCellValue As String
  
Sheets("ABC").Select
Set rng = Intersect(Range("D7:D10006"), ActiveSheet.UsedRange)
  For Each cell In rng
    strCellValue = (cell.Value)
    If InStr(strCellValue, "Dog") = 0 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
 
Sheets("XYZ").Select
Set rng = Intersect(Range("D7:D10006"), ActiveSheet.UsedRange)
  For Each cell In rng
    strCellValue = (cell.Value)
    If InStr(strCellValue, "Cat") = 0 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

 

[VoG]

Your On Error Resume Next may be masking errors. Try like this

Dim rng As Range, cell As Range, del As Range
  Dim strCellValue As String
  
Sheets("ABC").Select
Set rng = Intersect(Range("D7:D10006"), ActiveSheet.UsedRange)
  For Each cell In rng
    strCellValue = (cell.Value)
    If InStr(strCellValue, "Dog") = 0 Then
        If del Is Nothing Then
           Set del = cell
        Else: Set del = Union(del, cell)
        End If
     End If
  Next cell
  If del Is Nothing Then
    MsgBox "Nothing to delete on sheet " & ActiveSheet.Name
   Else
    del.EntireRow.Delete
   End If
 
Sheets("XYZ").Select
Set rng = Intersect(Range("D7:D10006"), ActiveSheet.UsedRange)
  For Each cell In rng
    strCellValue = (cell.Value)
    If InStr(strCellValue, "Cat") = 0 Then
        If del Is Nothing Then
           Set del = cell
        Else: Set del = Union(del, cell)
        End If
     End If
  Next cell
  If del Is Nothing Then
    MsgBox "Nothing to delete on sheet " & ActiveSheet.Name
   Else
    del.EntireRow.Delete
   End If

 

[Norie]

Try this.

Option Explicit
Sub test()
Dim ws As Worksheet
Dim rng As Range, cell As Range, del As Range
Dim strCellValue As String
 
    For Each ws In ThisWorkbook.Worksheets
 
        Select Case ws.Name
 
            Case "ABC", "XYZ"
 
                Set rng = Intersect(ws.Range("D7:D10006"), ws.UsedRange)
 
                For Each cell In rng
 
                    strCellValue = (cell.Value)
 
                    If InStr(strCellValue, "Dog") = 0 Then
 
                        If del Is Nothing Then
                            Set del = cell
                        Else
                            Set del = Union(del, cell)
                        End If
 
                    End If
 
                Next cell
 
               If Not del Is Nothing then del.EntireRow.Delete
 
            Case Else
                ' do nothing
        End Select
 
    Next ws
 
End Sub

It'll loop through all the worksheets but will only run the code on the 2 worksheets called ABC and XYZ.

You can easily alter the Select Case part to include/exclude worksheets, and there are probably other approachs.

The most important thing I suppose is the use of ws to refer to the worksheet(s) in the loop and the code.

That makes sure the code is applied to that right worksheet.

 

[JordanW]

Thanks, Salty Dog. I'll give this a go and come back if I am still having a problem.

 

[JordanW]

Thanks, Norie. This could work too.

 

[VoG]

Here's the reason for the error. You need to add the code in red after processing the first sheet.

If del Is Nothing Then
    MsgBox "Nothing to delete on sheet " & ActiveSheet.Name
   Else
    del.EntireRow.Delete
   End If
Set del = Nothing