Delete row with specific character in cell

davemorse

Board Regular
Joined
Mar 15, 2006
Messages
141
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
 

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
Joined
Apr 19, 2005
Messages
23,707
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
Joined
Mar 15, 2006
Messages
141
Hmm, doesnt seem to work
Can you talk me through which each part of the code means?

Thanks
 

Richard Schollar

MrExcel MVP
Joined
Apr 19, 2005
Messages
23,707
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
Joined
Mar 15, 2006
Messages
141

ADVERTISEMENT

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
Joined
Apr 19, 2005
Messages
23,707
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
Joined
Mar 15, 2006
Messages
141

ADVERTISEMENT

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
Joined
Apr 19, 2005
Messages
23,707
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
Joined
Apr 19, 2005
Messages
23,707
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.
 

Watch MrExcel Video

Forum statistics

Threads
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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top