Jim Thomlinson
12/14/2006 4:55:00 PM
Your code is very close... Give this a try...
Public Sub DeleteStuff()
Dim rngToSearch As Range
Dim rngFound As Range
Dim rngFoundAll As Range
Dim strFirst As String
Set rngToSearch = Sheets("Sheet1").Columns("I") 'Change this...
Set rngFound = rngToSearch.Find(What:="incomplete", _
LookIn:=xlFormulas, _
LookAt:=xlPart, _
MatchCase:=False)
If Not rngFound Is Nothing Then
Set rngFoundAll = rngFound
strFirst = rngFound.Address
Do
Set rngFoundAll = Union(rngFound, rngFoundAll)
Set rngFound = rngToSearch.FindNext(rngFound)
Loop Until rngFound.Address = strFirst
rngFoundAll.EntireRow.Delete
End If
End Sub
--
HTH...
Jim Thomlinson
"keri" wrote:
> I need to delete any rows where the value in column I is "INCOMPLETE".
> I tried writing the code myself but it wasn't recognising the cells
> that said "Incomplete". So I have searched the forums to find the code
> below which I thought would do the job with a bit of altering. However
> on stepping through the code it is not recognising cells that say
> INCOMPLETE either and is just continuing through the code without
> executing the delete row.
>
> Sub deleteincompleterows()
>
> Dim rng As Range, cell As Range, arrVar As Variant
> Dim strAddress1 As String, rngUnion As Range
> Set rng = Range("I5:I8")
>
> For Each arrVar In Array("INCOMPLETE*")
> Set cell = rng.Find(arrVar, LookIn:=xlValues, Lookat:=xlWhole,
> MatchCase:=True)
>
> If Not cell Is Nothing Then
> strAddress1 = cell.Address
> Do
> If rngUnion Is Nothing Then
> Set rngUnion = cell
> Else
> Set rngUnion = Union(cell, rngUnion)
> End If
> Set cell = rng.FindNext(cell)
> Loop While Not cell Is Nothing And _
> cell.Address <> strAddress1
> End If
> Next arrVar
> If Not rngUnion Is Nothing Then
> rngUnion.EntireRow.Delete
> End If
> End Sub
>
> Is this an obvious problem with my code or is it a problem with the
> values on my sheets?
>
>