| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Macro to Delete Rows if true HELP!
ACE!! it works now -- thanks!!!
|
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Macro to delete rows | Excel Programming | |||
| Macro to delete 2 rows every 500 rows | Excel Programming | |||
| Macro to delete rows | Excel Programming | |||
| macro to delete entire rows when column A is blank ...a quick macro | Excel Programming | |||
Hybrid Mode
