Macro to Delete Rows if true HELP!
 

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

Macro to Delete Rows if true HELP!
 

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.


"Mayte" wrote:

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

Macro to Delete Rows if true HELP!
 

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.


"Mike H" wrote:

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.


"Mayte" wrote:

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

Macro to Delete Rows if true HELP!
 

I got an error. the code stops at "stop" and doens't go forward

"Mike H" wrote:

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.


"Mayte" wrote:

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

Macro to Delete Rows if true HELP!
 

ACE!! it works now -- thanks!!!