LOOPS IN MACROS

[Heather O'Malley]

Hello

I'm trying to loop the below macro but having no luck.

I want in excel to find a certain word e.g. NOTNEEDED and delete whole line
then repeat until none left.

Any help gratefully accepted.

Range("A1").Select

Dim FoundCell As Range
With Worksheets("StockSheet")

Range("A1").Select

Set FoundCell = .Cells.Find(What:="NOTNEEDED", After:=ActiveCell, _
LookIn:=xlFormulas, LookAt:=xlPart, _
SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False)

If FoundCell Is Nothing Then

Else

FoundCell.EntireRow.Delete
End If

End With

 

[Ron de Bruin]

Hi Heather

See
http://www.rondebruin.nl/delete.htm

If you want to use Find try
http://www.rondebruin.nl/delete.htm#Find

 

[Gary''s Student]

This:

Sub cleanup()
Dim r As Range
Set r = ActiveSheet.UsedRange
nLastRoW = r.Rows.Count + r.Row - 1

For i = nLastRoW To 1 Step -1
If Cells(i, "A").Value = "NOTNEEDED" Then
Cells(i, "A").EntireRow.Delete
End If
Next
End Sub

will scan thru column A and remove rows with the match word.

 

[Don Guillett]

Using find look in the vba help for findNEXT. If in the same column you
could use data>filter>autofiter and delete all at once.Record a macro and
clean it up.