Find, Delete row, Loop

  • Thread starter Thread starter Dave Coz
  • Start date Start date
D

Dave Coz

Hello,
Trying to automate the following:
1. Find a string of characters ("apple") that apprear numerous times in the
spreadsheet.
2. When found delete the entire row.
3. Loop until the spreadsheet has no more instances of "Apple"

Many Thanks!
Dave
 
Option Explicit
Sub testme()
Dim FoundCell As Range
With ActiveSheet
Do
Set FoundCell = .Cells.Find(what:="Apple", _
after:=.Cells(1), _
LookIn:=xlFormulas, _
LookAt:=xlPart, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False)
If FoundCell Is Nothing Then
Exit Sub
Else
FoundCell.EntireRow.Delete
End If
Loop
End With
End Sub

You may want to change a few of those parms...

MatchCase:=false means that apple, APPLE, aPplE will all be found and deleted.

Lookat:=xlpart means that "Eve gave Adam an Apple in the garden" will be found
and deleted. Use xlWhole to find the cells that contain only the string (no
extr characters).

lookin:=xlformulas means that ="I have " & a1 & " apples" will be found and
deleted. But ="Ap"&"ple" won't be deleted.

You could use lookin:=xlvalues to find those kind of concatenated strings that
create the word "Apple".
 
Sub dural()
Dim rdel As Range
Set rdel = Nothing
For Each r In ActiveSheet.UsedRange
If r.Value = "oranges" Then
If rdel Is Nothing Then
Set rdel = r
Else
Set rdel = Union(rdel, r)
End If
End If
Next
rdel.EntireRow.Delete
End Sub
 
If oranges show up in cells on the same row, then the .delete statement will
fail.

You could use something like this instead:
Intersect(rdel.EntireRow, rdel.Parent.Columns(1)).EntireRow.Delete
 
Hi,

Loops take quite a bit of time to operate so the following is generally
50-100 times faster:

Sub DeleteOranges()
Dim Bottom As Long
Bottom = [A65536].End(xlUp).Row
Columns("B:B").Insert
Range("B1:B" & Bottom) = "=1/(RC[-1]=""orange"")"
Range("B1:B" & Bottom).SpecialCells(xlCellTypeFormulas,
1).EntireRow.Delete
Columns("B:B").Delete
End Sub

In this case I assumed the oranges were in column A.

If this helps, please click the Yes button

Cheers,
Shane Devenshire
 
Back
Top