Macro to Delete Rows if true HELP!

  • Thread starter Thread starter Mayte
  • Start date Start date
M

Mayte

I got this code from another answer and it works ok when I call it from the
activesheet ("Agent Tenure") but I need to call it from another sheet named
"Terminations"... please help????

Sub UPDATE_TENURE()
Dim myrange, MyRange1 As Range
lastrow = Cells(Rows.Count, "N").End(xlUp).Row
Set myrange = Sheets("Agent Tenure").Range("N1:N" & lastrow)
For Each c In myrange
If UCase(c.Value) = "TRUE" Then
If MyRange1 Is Nothing Then
Set MyRange1 = c.EntireRow
Else
Set MyRange1 = Union(MyRange1, c.EntireRow)
End If
End If
Next
If Not MyRange1 Is Nothing Then
MyRange1.Delete
End If

End Sub
 
Hi,

Try it like this

Sub UPDATE_TENURE()
Dim MyRange, MyRange1 As Range
Dim LastRow As Long
Set Sht = Sheets("Agent Tenure")
LastRow = Sht.Cells(Rows.Count, "N").End(xlUp).Row
Stop
Set MyRange = Sht.Range("N1:N" & LastRow)
For Each c In MyRange
If UCase(c.Value) = "TRUE" Then
If MyRange1 Is Nothing Then
Set MyRange1 = c.EntireRow
Else
Set MyRange1 = Union(MyRange1, c.EntireRow)
End If
End If
Next
If Not MyRange1 Is Nothing Then
MyRange1.Delete
End If
End Sub

--
Mike

When competing hypotheses are otherwise equal, adopt the hypothesis that
introduces the fewest assumptions while still sufficiently answering the
question.
 
Hmmm,

left a stop command in try this instead

Sub UPDATE_TENURE()
Dim MyRange, MyRange1 As Range
Dim LastRow As Long
Set Sht = Sheets("Agent Tenure")
LastRow = Sht.Cells(Rows.Count, "N").End(xlUp).Row
Set MyRange = Sht.Range("N1:N" & LastRow)
For Each c In MyRange
If UCase(c.Value) = "TRUE" Then
If MyRange1 Is Nothing Then
Set MyRange1 = c.EntireRow
Else
Set MyRange1 = Union(MyRange1, c.EntireRow)
End If
End If
Next
If Not MyRange1 Is Nothing Then
MyRange1.Delete
End If
End Sub
--
Mike

When competing hypotheses are otherwise equal, adopt the hypothesis that
introduces the fewest assumptions while still sufficiently answering the
question.
 
Back
Top