Delect all rows that contain an Error

[MarkCBB]

Hello,

I need some help completing this code.

Currently the code finds the range, fills the formula, the selects the errors (Using GoTo formulas, errors), it selects all the rows that contain the errors, but when I try to delete all those rows, it only deletes the first group on consecutive rows that contain the errors, the rest are all left there.

Sub CleanIT()
Dim FR As Range

Range("A2").Select
Range(Selection, Selection.End(xlDown)).Select
Set FR = Selection
FR.Offset(, 18).Select
Set FR = Selection
Selection.FormulaR1C1 = _
"=INDEX(OFFSET(STAFF_DB!R1C1,1,,COUNTA(STAFF_DB!C1)-1),MATCH(CLEANIT!R[128]C[-17],OFFSET(STAFF_DB!R1C2,1,,COUNTA(STAFF_DB!C2)-1),0))"
Selection.SpecialCells(xlCellTypeFormulas, 16).Select
Range(Selection, Selection.End(xlToLeft)).Select
Selection.Delete Shift:=xlUp
FR.Copy
FR.PasteSpecial Paste:=xlPasteValues
End Sub

if working correctly, this formula would delete all the rows that contain errors, in column S.

Please let me know if you require any further information regarding this, thanks

 

[VoG]

Try

Columns("S").SpecialCells(xlCellTypeFormulas, xlErrors).EntireRow.Delete

 

[MarkCBB]

Thasnk again Vog,

I had to change it a little, this is what I ended on: just a fyi.

Sub CleanIT()
Dim FR As Range

Range("A2").Select
Range(Selection, Selection.End(xlDown)).Select
Set FR = Selection
FR.Offset(, 18).Select
Set FR = Selection
Selection.FormulaR1C1 = _
"=INDEX(OFFSET(STAFF_DB!R1C1,1,,COUNTA(STAFF_DB!C1)-1),MATCH(CLEANIT!R[128]C[-17],OFFSET(STAFF_DB!R1C2,1,,COUNTA(STAFF_DB!C2)-1),0))"
FR.Copy
FR.PasteSpecial Paste:=xlPasteValues
Columns("S").SpecialCells(xlCellTypeConstants, 16).EntireRow.Delete

Thanks,