Find and delete macro

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Is it possible to create a macro that will search a worksheet for a key word and when it finds that word, will delete that entire row?
 
One way:

Option Explicit
Sub testme()

Dim myWord As String
myWord = "abc"

With ActiveSheet
On Error Resume Next
Do
.Cells.Find(What:=myWord, 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.
 
GA,

Here is a post from the archive where "relay" is deleted where ever it is
found in the column.

Unfortunately I didn't keep proper record of John's last name.

You could enlarge this column by changing range.

Hope this helps.

Regards,
Kevin

~~~~~~~~~~~~~~~~~~~~~
Try this...............

Sub DeleteRelay()
Dim c As Range

With Worksheets(1).Range("A:A")
Do
Set c = .Find("relay", LookIn:=xlValues, lookat:=xlPart, _
MatchCase:=False)
If c Is Nothing Then Exit Do
c.EntireRow.Delete
Loop
End With
End Sub

John

~~~~~~~~~~~~~~~~~~~~~~~~~


GA Hockey Mom said:
Is it possible to create a macro that will search a worksheet for a key
Click to expand...
word and when it finds that word, will delete that entire row?
 
Okay, I'm impressed. But let it be known that I'm just a step above a novice Excel user. Can you type the steps for me?
 
Hockey Mom

First!!!.....make sure you have a backup copy of your workbook.

With your workbook open in Excel......

ALT + F11 to open the Visual Basic Editor

On Menu go to View>Project Explorer(you may already be there).

Select your workbook/project by name.

Right-click on the project and select Insert>Module or on the Menu select
Insert>Module.

A blank window will be opened.

Copy the code from Dave's or Kevin's posting.

Paste it into the blank module. Substitute your word in place of "abc" by
normal editing methods. Leave the quote marks around your word.

File>Save

ALT + F11 to go back to Excel.

Tools>Macro>Macros

Select the macro by name and "Run"

That should generate a deletion of all rows which have been found with "the
word" therein.

When satisfied with the results. Save the workbook which will also save the
macro code.

For more info on getting started with Macros visit David McRithchie's site

http://www.mvps.org/dmcritchie/excel/getstarted.htm

Gord Dibben XL2002 and 11 years as a Hockey Dad but glad it's over.
 
Back
Top