Deleting rows when cell has "#DIV/0!" error

[mattg]

Hi,

I'm currently using this code to delete rows with balnk cells. However, i
have changes the formatting of my sheet and the cells are no longer blank
they have the "#DIV/0!" error. How can I delete the entire row when the cell
in column D has that value?

Sub DeleteRowsIfDIsBlank()

With payrollsht

Dim rg As Range, rgBlank As Range

Set rg = Cells.Range("D")

On Error Resume Next

Set rgBlank = rg.SpecialCells(xlCellTypeBlanks)

On Error GoTo 0

If rgBlank Is Nothing Then

Else

rgBlank.EntireRow.Delete

End If

End With

End Sub

 

[Wouter HM]

Hi matt,

In Excel 2003 I creates this:

Sub DeleteRowsIfDIsError()

Dim i As Integer
With ActiveSheet
' start with last used row
For i = Cells(65536, 4).End(xlUp).Row _
To 1 Step -1
If IsError(Cells(i, 4).Value) Then
Rows(i).EntireRow.Delete
End If
Next
End With
End Sub

HTH,

Wouter

 

[Project Mangler]

Hi Mattg,

If #DIV/0! is the only error in Column D you could try a loop with

Set rgError = rg.SpecialCells(xlCellTypeFormulas, xlErrors)

or something like that (bearing in mind the limitations of Specialcells)

HTH