Macro to Delete Rows if true HELP!

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
 
M

Mike H

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.
 
M

Mike H

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.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top