Delete Rows Without Specific Text

[waggett]

Hi Guru's, I need help.

I have a spreadsheet with 12 columns and 54,000 rows. Some of those rows
have a specific word within a cell, and sometimes that word occurs in more
than one cell within the row.

I would like to delete all rows which do not have that word in them, leaving
only the rows I care about. What is the best way to do this?

 

[Jacob Skaria]

You can try out the below macro. If you are new to macros..

--Set the Security level to low/medium in (Tools|Macro|Security).
--From workbook launch VBE using short-key Alt+F11.
--From menu 'Insert' a module and paste the below code.
--Get back to Workbook.
--Run macro from Tools|Macro|Run <selected macro()>

--Change the search string which is currently specified as jacob
--If you are looking for a whole cell match change LookAt:=xlPart to
LookAt:=xlWhole

Sub Macro1()
Dim lngRow As Long, lngLastRow As Long, varFound As Range
lngLastRow = Cells.Find(What:="*", SearchDirection:=xlPrevious, _
SearchOrder:=xlRows).Row
For lngRow = lngLastRow To 2 Step -1
Set varRange = Rows(lngRow).Find(What:="jacob", LookAt:=xlPart)
If varRange Is Nothing Then Rows(lngRow).Delete
Next
End Sub

If this post helps click Yes

 

[waggett]

Jacob,

The first time I ran it I substituted the word "vector"for "jacob" in line
six and it deleted everything. Then I tried also replacing the asterisk in
line three with the word "vector" as well and it worked. Many thanks, you
saved me a ton of work.

Tagg

-----------------------

 

[Ashish Mathur]

Hi,

Suppose you want to exclude all rows which contain the word Good. Assume
your data is in range A5:O54004. In A4:O4, type in the headings. In A2,
type =COUNTIF(A5:O5,"*Good*")=0.

Now go to Filter > Advanced Filter > Copy to another location. In the List
range box, refer A5:O54004, in the criteria box, refer A1:A2. In the copy
to box, give the reference of any blank cell on the same sheet.

Now click on OK.

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com

 

[waggett]

One little quirk, the macro doesn't delete the rows that don't have the word
"vector" in them, it places them below all the rows that do have the word
"vector" in them.

 

[Jacob Skaria]

The 3rd line is to get the last used row. You dont need to edit that; If you
have edited that then the code returns the last row which contain the word
vector...Try the below..

Sub Macro1()
Dim lngRow As Long, lngLastRow As Long, varFound As Range
lngLastRow = Cells.Find(What:="*", SearchDirection:=xlPrevious, _
SearchOrder:=xlRows).Row

For lngRow = lngLastRow To 2 Step -1
Set varRange = Rows(lngRow).Find(What:="vector", LookAt:=xlPart)
If varRange Is Nothing Then Rows(lngRow).Delete
Next
End Sub

If this post helps click Yes

 

[waggett]

This time it works perfectly, thanks again Jacob.

Tagg