Solved: VBA to Delete rows using IsError

[JimS]

I'm trying to delete the entire row if a formula in column A has a #REF! error. The range starts at A5 and could be up to 200 rows.

This seems like an easy one but I have been able to make it work.

Thanks...

Jim


Sub DelRefErrors()
Dim ColNum As Integer
Sheets("Metrics").Select

'Find Last Row with Data in Column A
For ColNum = 1 To 1
NxtLastRow = Cells(Rows.Count, ColNum).End(xlUp).Row
If NxtLastRow > LastRow Then LastRow = NxtLastRow
Next
'Check Column A for Formula Errors
For DelRow = LastRow To 1 Step -1
If (Cells(DelRow, 1) IsError Then <<< Doesn't like this line >>>>

End If
Next


End Sub

[mdmackillop]

Hi Jim
Just a simple switch

If IsError(Cells(DelRow, 1)) Then


Check out SpecialCells for another method.

[JimS]

I guess I'm going about this the wrong way.

This is the formula that is in Column A (in a few cells) that I'm trying to delete the row for.

I don't think the IsError is detecting it.

=if('Input'!#REF!="","",'Input'!#REF!)

[p45cal]

are you missing a statement like the one in red?:
For DelRow = LastRow To 1 Step -1
If IsError(Cells(DelRow, 1)) Then '<<< Doesn 't like this line >>>>
Rows(DelRow).Delete
End If

[JimS]

I added that but it still doesn't delete the rows with =if('Input'!#REF!="","",'Input'!#REF!) in Column A

[JimS]

Looks like I have another issue.

This code works when I manually run it, but not when it's called by another routine.

[p45cal]

Works fine here in Excel 2003, run manually or called from another routine.
Which line is it failing on? What's the error message. How are you calling it? Step through the sub and make sure it's doing as you expect.

[mikerickson]

Range("A:A").SpecialCells(xlCellTypeFormulas, xlErrors).EntireRow.Delete

[JimS]

It works now, CTKI (chair to keyboard issue).

Thanks everyone...