By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
425,790 Members | 1,409 Online
Bytes IT Community
+ Ask a Question
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
  1. Sub DeleteZDEWs()
  2. Dim iLastRow As Long
  3.     Dim i As Long
  4.     iLastRow = Cells(Rows.Count, "H").End(xlUp).Row
  5. For i = iLastRow To 2 Step -1
  6.         If Cells(i, "H").Value = "ZDEW" And Cells(i, "A").Value <> "W25G1*" Then
  7.     Rows(i).Delete
  8.     End If
  9. Next i
  10.     'deletes row when cell in Column H is a ZDEW and cell in Column A does not start with W25G1
  11. End Sub
May 8 '12 #1

✓ answered by NeoPa

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

Share this Question
Share on Google+
4 Replies


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

NeoPa
Expert Mod 15k+
P: 31,418
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
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

NeoPa
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

Post your reply

Sign in to post your reply or Sign up for a free account.