error in macro (next related i think)

  • Thread starter Thread starter steven
  • Start date Start date
S

steven

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.
 
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
Click to expand...

Probably the cells cannot contain "overhead" AND
"OPERATIONS" but OR the First OR the second.
Regards
Eliano
 
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.
 
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.
 
Hi JL.

Probably, due to my poor english, i have not understood the question.
Many thanks & regards,
Eliano
 
Back
Top