Good Evening OssieMac:

While waiting for a reply, I came up with this Macro that seems to work...Do
you agree? If not, the next time I run the report, I will try your method.

This is the Macro that I used that seemed to work OK...What do you think?

Sub DeleteLOATermedDupRecords()
'
mc = "J"
For i = Cells(Rows.Count, mc).End(xlUp).Row To 1 Step -1
If Cells(i, "I") = "0" Then
If Cells(i, mc) = "LOA" _
Or Cells(i, mc) = "Termed" _
Or Cells(i, mc) = "Duplicate" Then
Rows(i).Delete
End If
End If
Next i
'
End Sub

--
jeannie v


"OssieMac" wrote:

Hi Jeannie,

I haven't gone into you macro to see exactly what it is doing but when you
are deleting rows, you should always work backwards from the last row
otherwise you will actually miss rows after a row is deleted.

Try changing the For Next loop to
For i = r to 1 step -1


--
Regards,

OssieMac


"jeannie v" wrote:

Hi Expert:

I used this Macro again and found that it doesn't delete all the Zero Counts
in Column I...I tried it with 20 Lines some with "0" in Column I and some
with "1" in Column I...When I run the Macro, it only deletes some of the Zero
Counts in Column I.....FOR LOA, Termed and Duplicates...I have other Status
in Column J and I want them to remain, even if they are Zero Counts in Column
I...I also need it to delete the rows faster which I thought the Calc
Commands would have done that.

Sub DeleteLOATermedDuplicate()

mc = "J"
r = Cells(Rows.Count, mc).End(xlUp).Row
For i = 1 To r
If Cells(i, "I") = "0" Then
If Cells(i, mc) = "LOA" _
Or Cells(i, mc) = "Termed" _
Or Cells(i, mc) = "Duplicate" Then
Rows(i).Delete
End If
End If
Next i
End Sub

I can't figure out what is wrong...I hope you can help me...Thank you.


--
jeannie v


"FSt1" wrote:

hi
this works for me(several times) on a ten row test in xl2003...

mc = "J"
r = Cells(Rows.Count, mc).End(xlUp).Row
For i = 1 To r
If Cells(i, "I") = "0" Then
If Cells(i, mc) = "LOA" _
Or Cells(i, mc) = "Termed" _
Or Cells(i, mc) = "Duplicate" Then
Rows(i).Delete
End If
End If
Next i

regards
FSt1

"jeannie v" wrote:

Good Evening, Experts:

I want this macro to remove all Zero (as Text) in Column I for LOA, Termed
and Duplicate records in Column J...My macro is not working...can someone
tell me the commands to make this work. There is an error somewhere and I'm
not savvy enough to fix it.

Macro is:

Sub DeleteLOATermedDupRecords()

Application.Calculation = xlCalculationManual
mc = "J"
For i = Cells(Rows.Count, mc).End(xlUp).Row To 1 Step -1
If Cells(i, "I") = "0" Then
Cells(i, mc) = "LOA" Or Cells(i, mc) = "Termed" Or _
Cells(i, mc) = "Duplicate"
Rows(i).Delete
Next i
Application.Calculation = xlCalculationAutomatic
End Sub


Thank you for your help
--
jeannie v