# Delete row with specific character in cell

#### davemorse

##### Board Regular
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.

### Excel Facts

How to create a cell-sized chart?
Tiny charts, called Sparklines, were added to Excel 2010. Look for Sparklines on the Insert tab.

#### Richard Schollar

##### MrExcel MVP
Something like:

Code:
``````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

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

Thanks

#### Richard Schollar

##### MrExcel MVP
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?

Code:
``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).

Code:
`` 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.

Code:
``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

##### Board Regular

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

##### MrExcel MVP
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

##### Board Regular

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

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

????X*

And in VBA something like:

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

Richard

#### Richard Schollar

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

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

It's very quick.

Replies
1
Views
94
Replies
3
Views
296
Replies
2
Views
156
Replies
13
Views
445
Replies
0
Views
92

1,133,459
Messages
5,658,893
Members
418,475
Latest member
ExcelBeginner233

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back