425,790 Members | 1,409 Online
Need help? Post your question and get tips & solutions from a community of 425,790 IT Pros & Developers. It's quick & easy.

# Excel: How do I Delete Rows on Two Conditions

 P: 2 I am trying to delete rows that contain ZDEW in column H and do not start with W25G1 in column A. I used an asterik to show other letters/numbers may follow W25G1, but it's deleting all the ZDEW rows, including the ones that start with W25G1. Here is what I have. Where did I go wrong? Expand|Select|Wrap|Line Numbers Sub DeleteZDEWs() Dim iLastRow As Long     Dim i As Long     iLastRow = Cells(Rows.Count, "H").End(xlUp).Row For i = iLastRow To 2 Step -1         If Cells(i, "H").Value = "ZDEW" And Cells(i, "A").Value <> "W25G1*" Then     Rows(i).Delete     End If Next i     'deletes row when cell in Column H is a ZDEW and cell in Column A does not start with W25G1 End Sub May 8 '12 #1

First off, line #4 is unreliable. It should say :
Expand|Select|Wrap|Line Numbers
1. iLastRow = Range("A1").SpecialCells(xlLastCell).Row
Lines #6 & #7 could be redone in either of two ways as :
1. Expand|Select|Wrap|Line Numbers
1. If (Cells(i, "H") = "ZDEW") _
2. And (Left(Cells(i, "A"), 5) <> "W25G1") Then Call Rows(i).Delete
2. Expand|Select|Wrap|Line Numbers
1. If (Cells(i, "H") = "ZDEW") _
2. And (Cells(i, "A") Not Like "W25G1*") Then Call Rows(i).Delete

4 Replies

 Expert Mod 100+ P: 2,321 The asterisk cannot be used as a joker in that context. Instead you could use the `left`function, that should suit your needs just fine. May 8 '12 #2

 Expert Mod 15k+ P: 31,418 First off, line #4 is unreliable. It should say : Expand|Select|Wrap|Line Numbers iLastRow = Range("A1").SpecialCells(xlLastCell).Row Lines #6 & #7 could be redone in either of two ways as : Expand|Select|Wrap|Line Numbers If (Cells(i, "H") = "ZDEW") _ And (Left(Cells(i, "A"), 5) <> "W25G1") Then Call Rows(i).Delete Expand|Select|Wrap|Line Numbers If (Cells(i, "H") = "ZDEW") _ And (Cells(i, "A") Not Like "W25G1*") Then Call Rows(i).Delete May 9 '12 #3

 P: 2 Thank ya'll so much! I am very new at this and don't know how to insert a function, so I couldn't test the Left function suggestion. However, the first option to fix lines 6 & 7 worked! (Also, I don't know how to add code tags.) A million thank yous! May 9 '12 #4

 Expert Mod 15k+ P: 31,418 You're welcome Cloggy :-) In fact, the first option, the one you chose, is an example of using the Left() function. It really is as easy as that. Cloggy42: (Also, I don't know how to add code tags.) Please visit [code] Tags Must be Used and Before Posting (VBA or SQL) Code. They will fill you in on that and other important points on how to deal with code here. May 9 '12 #5