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

Did you know Excel offers Filter by Selection?
Add the AutoFilter icon to the Quick Access Toolbar. Select a cell containing Apple, click AutoFilter, and you will get all rows with Apple

Chitosunday

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

Watch MrExcel Video

Forum statistics

Threads
1,129,800
Messages
5,638,430
Members
417,025
Latest member
MusterDuster

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