Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
error in macro (next related i think)
Hi, i use the following macro to delete rows from a worksheet
Sheets("MOBILE TOTAL").Select lr = Cells(Rows.Count, "e").End(xlUp).Row For i = lr To 2 Step -1 If Cells(i, "e") < "overhead" And _ Cells(i, "e") < "OPERATIONS" Then Rows(i).Delete Next i eveerything works fine, now i want to use it to delete rows 4 differest worksheets in the same workbook so i figure i make it like this: Sub MOBILEKAPNISIS() Sheets("MOBILE TOTAL").Select lr = Cells(Rows.Count, "e").End(xlUp).Row For i = lr To 2 Step -1 If Cells(i, "e") < "overhead" And _ Cells(i, "e") < "OPERATIONS" Then Rows(i).Delete Next i Sheets("VOICE TOTAL").Select lr = Cells(Rows.Count, "e").End(xlUp).Row For i = lr To 2 Step -1 If Cells(i, "e") < "overhead" And _ Cells(i, "e") < "OPERATIONS" Then Rows(i).Delete Next i Sheets("BLACKBERRY TOTAL").Select lr = Cells(Rows.Count, "g").End(xlUp).Row For i = lr To 2 Step -1 If Cells(i, "g") < "overhead" And _ Cells(i, "g") < "OPERATIONS" Then Rows(i).Delete Next i Sheets("DATA TOTAL").Select lr = Cells(Rows.Count, "g").End(xlUp).Row For i = lr To 2 Step -1 If Cells(i, "g") < "overhead" And _ Cells(i, "g") < "OPERATIONS" Then Rows(i).Delete Next i End Sub but nope it dosent work, anyone can help me out? thanks in advance. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
error in macro (next related i think)
Hi steven.
Try to change: If Cells(i, "e") < "overhead" And _ Cells(i, "e") < "OPERATIONS" Then Rows(i).Delete as: If Cells(i, "e") < "overhead" Or _ Cells(i, "e") < "OPERATIONS" Then Rows(i).Delete Probably the cells cannot contain "overhead" AND "OPERATIONS" but OR the First OR the second. Regards Eliano On 3 Giu, 23:31, steven wrote: Hi, i use the following macro to delete rows from a worksheet Sheets("MOBILE TOTAL").Select lr = Cells(Rows.Count, "e").End(xlUp).Row For i = lr To 2 Step -1 If Cells(i, "e") < "overhead" And _ Cells(i, "e") < "OPERATIONS" Then Rows(i).Delete Next i eveerything works fine, now i want to use it to delete rows 4 differest worksheets in the same workbook so i figure i make it like this: Sub MOBILEKAPNISIS() Sheets("MOBILE TOTAL").Select lr = Cells(Rows.Count, "e").End(xlUp).Row For i = lr To 2 Step -1 If Cells(i, "e") < "overhead" And _ Cells(i, "e") < "OPERATIONS" Then Rows(i).Delete Next i Sheets("VOICE TOTAL").Select lr = Cells(Rows.Count, "e").End(xlUp).Row For i = lr To 2 Step -1 If Cells(i, "e") < "overhead" And _ Cells(i, "e") < "OPERATIONS" Then Rows(i).Delete Next i Sheets("BLACKBERRY TOTAL").Select lr = Cells(Rows.Count, "g").End(xlUp).Row For i = lr To 2 Step -1 If Cells(i, "g") < "overhead" And _ Cells(i, "g") < "OPERATIONS" Then Rows(i).Delete Next i Sheets("DATA TOTAL").Select lr = Cells(Rows.Count, "g").End(xlUp).Row For i = lr To 2 Step -1 If Cells(i, "g") < "overhead" And _ Cells(i, "g") < "OPERATIONS" Then Rows(i).Delete Next i End Sub but nope it dosent work, anyone can help me out? thanks in advance. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
error in macro (next related i think)
Your code works. I set up as test with data in columns E and G and it
deleted the rows that did not have overhead or OPERATIONS in those columns. The problem might be in the assignment of the last row. Try using the sheet designation for each variable assignment for the last row like this example. lr = Sheets(MOBILE TOTAL").Cells(Rows.Count, "E").End(xlUp).Row When I checked the last row in my sample, using your original code, it was still using the last row for column E on a worksheet that should have been for column G. If column E had been blank then it would have done nothing. By qualifying the Cells designation with the worksheet, it then used column g to find the last row. Check it out and see if it solves the problem. If not, then post back. "steven" wrote: Hi, i use the following macro to delete rows from a worksheet Sheets("MOBILE TOTAL").Select lr = Cells(Rows.Count, "e").End(xlUp).Row For i = lr To 2 Step -1 If Cells(i, "e") < "overhead" And _ Cells(i, "e") < "OPERATIONS" Then Rows(i).Delete Next i eveerything works fine, now i want to use it to delete rows 4 differest worksheets in the same workbook so i figure i make it like this: Sub MOBILEKAPNISIS() Sheets("MOBILE TOTAL").Select lr = Cells(Rows.Count, "e").End(xlUp).Row For i = lr To 2 Step -1 If Cells(i, "e") < "overhead" And _ Cells(i, "e") < "OPERATIONS" Then Rows(i).Delete Next i Sheets("VOICE TOTAL").Select lr = Cells(Rows.Count, "e").End(xlUp).Row For i = lr To 2 Step -1 If Cells(i, "e") < "overhead" And _ Cells(i, "e") < "OPERATIONS" Then Rows(i).Delete Next i Sheets("BLACKBERRY TOTAL").Select lr = Cells(Rows.Count, "g").End(xlUp).Row For i = lr To 2 Step -1 If Cells(i, "g") < "overhead" And _ Cells(i, "g") < "OPERATIONS" Then Rows(i).Delete Next i Sheets("DATA TOTAL").Select lr = Cells(Rows.Count, "g").End(xlUp).Row For i = lr To 2 Step -1 If Cells(i, "g") < "overhead" And _ Cells(i, "g") < "OPERATIONS" Then Rows(i).Delete Next i End Sub but nope it dosent work, anyone can help me out? thanks in advance. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
error in macro (next related i think)
I'm not so sure that is a good idea. eliano. Using Or would cause it to
equal True if one of the words appear in the cell. I believe he wants it to be true only if neither of the the words appear. "eliano" wrote: Hi steven. Try to change: If Cells(i, "e") < "overhead" And _ Cells(i, "e") < "OPERATIONS" Then Rows(i).Delete as: If Cells(i, "e") < "overhead" Or _ Cells(i, "e") < "OPERATIONS" Then Rows(i).Delete Probably the cells cannot contain "overhead" AND "OPERATIONS" but OR the First OR the second. Regards Eliano On 3 Giu, 23:31, steven wrote: Hi, i use the following macro to delete rows from a worksheet Sheets("MOBILE TOTAL").Select lr = Cells(Rows.Count, "e").End(xlUp).Row For i = lr To 2 Step -1 If Cells(i, "e") < "overhead" And _ Cells(i, "e") < "OPERATIONS" Then Rows(i).Delete Next i eveerything works fine, now i want to use it to delete rows 4 differest worksheets in the same workbook so i figure i make it like this: Sub MOBILEKAPNISIS() Sheets("MOBILE TOTAL").Select lr = Cells(Rows.Count, "e").End(xlUp).Row For i = lr To 2 Step -1 If Cells(i, "e") < "overhead" And _ Cells(i, "e") < "OPERATIONS" Then Rows(i).Delete Next i Sheets("VOICE TOTAL").Select lr = Cells(Rows.Count, "e").End(xlUp).Row For i = lr To 2 Step -1 If Cells(i, "e") < "overhead" And _ Cells(i, "e") < "OPERATIONS" Then Rows(i).Delete Next i Sheets("BLACKBERRY TOTAL").Select lr = Cells(Rows.Count, "g").End(xlUp).Row For i = lr To 2 Step -1 If Cells(i, "g") < "overhead" And _ Cells(i, "g") < "OPERATIONS" Then Rows(i).Delete Next i Sheets("DATA TOTAL").Select lr = Cells(Rows.Count, "g").End(xlUp).Row For i = lr To 2 Step -1 If Cells(i, "g") < "overhead" And _ Cells(i, "g") < "OPERATIONS" Then Rows(i).Delete Next i End Sub but nope it dosent work, anyone can help me out? thanks in advance. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
error in macro (next related i think)
Hi JL.
Probably, due to my poor english, i have not understood the question. Many thanks & regards, Eliano On 4 Giu, 05:41, JLGWhiz wrote: I'm not so sure that is a good idea.eliano. *Using Or would cause it to equal True if one of the words appear in the cell. *I believe he wants it to be true only if neither of the the words appear. "eliano" wrote: Hi steven. Try to change: If Cells(i, "e") < "overhead" And _ Cells(i, "e") < "OPERATIONS" Then Rows(i).Delete as: If Cells(i, "e") < "overhead" Or _ Cells(i, "e") < "OPERATIONS" Then Rows(i).Delete Probably the cells cannot contain "overhead" AND "OPERATIONS" but OR the First OR the second. Regards Eliano |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Run time error related to add-ins | Excel Discussion (Misc queries) | |||
related to error function | Excel Discussion (Misc queries) | |||
Error trapping is not working for errors related to files | Excel Programming | |||
Syntax error - FSO related? | Excel Programming | |||
macro related | Excel Discussion (Misc queries) |