Recognise text to remove in VBA

Fletchmeister

Board Regular
Joined
May 20, 2003
Messages
114
This is probably a simple one, I have a selection of data that is ever increasing. I want some code that will recoginse if certain cell contain the word "Blank" if they do then I want it to delete those rows.

Any ideas?

Fletch (y)
 

Excel Facts

Excel Wisdom
Using a mouse in Excel is the work equivalent of wearing a lanyard when you first get to college
select the cells and name it "celnotblank". The is the code:

For Each cel In [celnotblank]
If LCase(cel) = "blank" Then cel.EntireRow.Delete
Next cel
 
Upvote 0
Hi Fletchmeister

Chitosunday is correct in that a Range called "celnotblank" could be searched and those rows that apply will be deleted.

However, ther are a whole bunch of ways to select a range of cells that would be searched for the offending word. Chitosundays' way would only search cells within the Named Range. Another way would be to allow you to select the range you wish to search, like so:

Code:
Sub LoseThemRows()
Dim c
Dim i As String
Dim j As String

Dim xlWSheet As WorkSheet
Set xlWSheet = ActiveSheet

i = ActiveWindow.RangeSelection.Address

For Each c in xlWSheet.Range(i).Cells
j = c.Value
If LCase(j) = "blank" Then
Activecell.EntireRow.Delete
ActiveCell.Offset(1,0).Activate
Next c

End Sub

Try That.

anvil19
:eek:
 
Upvote 0
Thanks for that guys but i've just come up with another way that works perfect for me by using the AutoFilter routine to filter for the word blank then delete that selection. It works really well.

Thanks for you solution though I will still keep them as reference,

Fletch (y) (y)
 
Upvote 0

Forum statistics

Threads
1,217,498
Messages
6,136,993
Members
450,037
Latest member
Tao86

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
Back
Top