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

Chitosunday

Well-known Member
Joined
Jul 14, 2003
Messages
1,023
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
 

anvil19

Board Regular
Joined
Nov 18, 2003
Messages
230
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:
 

Fletchmeister

Board Regular
Joined
May 20, 2003
Messages
114
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)
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,167,966
Messages
5,856,560
Members
431,818
Latest member
Helpmyassignment

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