keri
12/20/2006 11:50:00 AM
I have the below code kindly provided to me by a user of this forum
Sub deleteincomplete()
'delete all rows that have incomplete data
Dim rngToSearch As Range
Dim rngFound As Range
Dim rngFoundAll As Range
Dim strFirst As String
Dim i As Integer
For i = 1 To answer
Set rngToSearch = Sheets("cardata" & i).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
Next i
The idea is that if one of my rows that are being searched contains
"incomplete" that the whole row is deleted. I have now found I need to
keep this row, but delete where is says incomplete and enter the word
"incomplete" into column B. I then need to copy the data from columns C
+ E + G on this row into column I.
I am not sure where to start writing this code into the rngfoundAll row
in the code above.
Many thanks.