setting up "find and delete row" macro?

[Doug]

I'm using Excel 2000. I've been trying to set up a macro
to perform a loop function: 1) "find next" the occurrence
of a specific text string 2) finding the text string in a
cell, select the entire row 3) delete the entire row 4)
find next occurrence of specific text string and repeat
the process.

So basically, I want to search an entire worksheet for
every occurrence of the string "abc" for example -- and
delete every row with "abc" in it, but leave all rows that
do NOT have "abc" in them.

I've been trying to set up a macro for that, but it's not
working. Maybe I am not setting up the macro correctly?
Or there is a non-macro way to accomplish the same thing?

Thanks in advance for any advice & info!

 

[Dave Peterson]

ABC in any cell in any row?

Option Explicit
Sub testme()

Dim myWord As String
myWord = "abc"

With ActiveSheet
On Error Resume Next
Do
.Cells.Find(What:="asdf", After:=.Cells(.Cells.Count), _
LookIn:=xlFormulas, LookAt:=xlPart, _
SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False).EntireRow.Delete
If Err.Number <> 0 Then Exit Do
Loop
On Error GoTo 0
End With
End Sub

It just tries to find it/delete it until it can't find it anymore and throws an
error.

 

[Doug]

Hi Dave -

Thanks for the suggestion! I'm not an advanced enough
Excel guy to know where to put this kind of code, but I'll
see if anyone in the office might know. Otherwise, I will
just keep hacking away at it manually.

Thanks again!

 

[Dave Peterson]

If you're doing it manually and you only have one column to inspect, you could
apply Data|filter|autofilter.

then filter on that column, but use custom, then contains (or equals???) ABC.

Then delete those visible rows.

If you have multiple columns to look at, you could insert a helper column (I'll
use column A) and use a worksheet formula:

=if(countif(b1:iv1,"abc")>0,"delete","keep")
drag down and do the data|Filter|autofilter on that helper column.
Filter by Delete and then delete the visible rows.
then delete that helper column.

If ABC was embedded in a cell (like: Company Name: ABC), then use this
variation:
=if(countif(b1:iv1,"*abc*")>0,"delete","keep")

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm


Short course:
Hit alt-f11 to get to the VBE (where macros/UDF's live)
hit ctrl-R to view the project explorer
Find your workbook.
should look like: VBAProject (yourfilename.xls)

right click on the project name
Insert, then Module
You should see the code window pop up on the right hand side

Paste the code in there.

Now go back to excel.
click on Tools|macro|macros...
click on the macro name (testme--but you could rename it to something
meaningful!)
and then click run.