Delete row with specific character in cell

[davemorse]

Hi,

How would I write some VB to delete any row with an "x" in the corresponding cell in column B. I.e if B4 and B10 has an "x" in it, i would like to delete row 4 and row 10.

Many Thanks for your help

 

[Richard Schollar]

Something like:

Sub testit()
For i = 10 to 4 step -1
 If Cells(i,"B").Value = "x" Then Cells(i,"B").EntireRow.Delete
Next i
End Sub

Best regards

Richard

 

[davemorse]

Hmm, doesnt seem to work
Can you talk me through which each part of the code means?

Thanks

 

[Richard Schollar]

Dave

What exactly do you have in each cell? Is it a discrete 'x' on its own, or is there other stuff in there as well?

For i = 10 to 4 step -1

we step backwards thru the row numbers from last to first (this avoids problems caused by deleting rows).

 If Cells(i,"B").Value = "x" Then Cells(i,"B").EntireRow.Delete

Checks the value in the cell (for the first pass, i=10 so this is cell "B10"). If value in B10 is "x" then delete the entire row.

Next i

Move on to the next value of i (ie effectively up a row to the next cell... "B9" next and so on until "B4").

Does this help?

Richard

 

[davemorse]

Ah many thanks, Its case sensitive.

One last thing..... I'd like to amend this to look in each cell in col B and see if the the fifth character was an "X" and delete that row. How would I do this?
Ive tried this but to no avail....

If mid (Cells(i, "B").Value),5,1) = "X" etc

Many Thanks

 

[Richard Schollar]

Dave

You have used exactly the right syntax there (altho you do appear to have a spurious ")" in there after Value) - what error are you getting?

By the way, if you are doing this check for more than a couple of hundred rows say, there are better ways to automate the find/delete operation (Autofilter being my method of choice). This can be macro'ed up and will work faster.

Richard

 

[davemorse]

is it possible to specify in the Autofilter to discard if there is an "X" in the fifth character. I assumed this was too specific...

 

[Richard Schollar]

Yep - in Excel you'd specify the criteria (custom) as:

????X*

And in VBA something like:

Range("A1:D1000").Autofilter Field:=1,Criteria1:="????X*"

Richard

 

[Richard Schollar]

Sorry, then meant to say you can delete the autofiltered results by using the SpecialCells method:

...
Range("A1:D1000").SpecialCells(xlCellTypeVisible).EntireRow.Delete

It's very quick.