Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Missing a command in this macro
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Missing a command in this macro
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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Missing a command in this macro
Hi Expert....Thank you....yes it does work....I was adding the Auto Calc
Manual and Auto commands and I guess thatis goobering it up...When I use the Macro as you did, it only does 1 row at a time...since I have 12000 rows, it takes forever...I'm trying to get the Macro to go through the doc fast...Can you give me any suggestions? Sub DeleteLOATermedDupRecords() Application.Calculation = xlCalculationManual 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 Application.Calculation = xlCalculationAutomatic End Sub I appreciate your expertise.....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 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Missing a command in this macro
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 |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Missing a command in this macro
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 |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Missing a command in this macro
hi
the loop you were trying to do only evaluates 1 row at a time. "for each" ie 1 row at a time. from your first post, i thought you were only concerned about "work" and "don't work", so i provided "work". 12000 rows will add time to "work". as to deleting zeros. the code only evaluate each row but only deletes if status LOA, Termed and duplicate are ment. other statuses should not be effected. so i am confused on this point. and ozziemac is right. i missed that point. oops. regards FSt1 "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 |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Missing a command in this macro
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 |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Missing a command in this macro
Hi Expert:
Thank you fo rall your help...It got me on the right track! This is what I came up with that works: 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 "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 |
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Missing a command in this macro
Yes Jeannie; it looks like you have got it now. I hope you also understand
the logic of working backwards from the bottom when deleting rows? -- Regards, OssieMac "jeannie v" wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
repeat command missing | Excel Discussion (Misc queries) | |||
Data XML Command Missing | Excel Discussion (Misc queries) | |||
Command button missing on template | Excel Discussion (Misc queries) | |||
Office2000: Missing Print command in File menu | Excel Discussion (Misc queries) | |||
Excel 2003 - Toolbar missing the file command | Excel Discussion (Misc queries) |