Delete row after finding cell.

[gjsala]

I would like to delete a row after the vba code identified the the matching cell. The code is as follows:

For r = 1 To 6500
If Len(Cells(r, 8)) = "FAIL" Then
Exit For ' gets out of the loop
End If
If Len(Cells(r, 8)) = 4 Then

***I need something here to
delete the row found and then
moves the data row below it
up to it's place. ***

End If
Next r

Thanks for your help!

 

[Gavona]

Not tested (excel not currently available) but I think this should do it:
ActiveSheet.Cells(r, 8).EntireRow.Delete

Incidentally, I suspect that your code could be speeded up if you used autofilter. From memory the steps would be:
* Macrorecorder on
* Select cloumn 8
* set the custom criteria to not equal to fail,
* highlight the data area (find a column that definitely contains data in every row, cursor in row 1, Ctrl-Shift-Down),
* Edit,Goto,Special,Visiblecells only (not sure if this is necessary but it feels safe!)
* Edit,delete,entire row

 

[xlbo]

I'd suggest that you wanna move UPWARDS in your loop otherwise you will miss rows due to the incrementing value
For r = 6500 to 1 step -1

line used for deletion could be

rows(r).entirerow.delete shift:= xlup

don't think this can be achieved using autofilter as you can't test for the length of a cell in autofilter Rgds
Geoff
Human beings, who are almost unique in having the ability to learn from the experience of others, are also remarkable for their apparent disinclination to do so. Douglas Adams

 

[shager]

UNTESTED


Another Option would be to add each line you wish to delete to a range then zap the range at the same time....



Dim MyRange As Range
Dim AnyRangeFound As Boolean

AnyRangeFound = False



For r = 1 To 6500
If Len(Cells(r, 8)) = "FAIL" Then
Exit For ' gets out of the loop
End If
If Len(Cells(r, 8)) = 4 Then

If AnyRangeFound = False Then
Set MyRange = Cells(r, 8)
AnyRangeFound = True
Else
Set MyRange = Union(MyRange, Cells(r, 8))
End If

End If

Next r


MyRange.Enitrerow.Delete

 

[gjsala]

Thanks for all of your help, these look good!!!!!