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

M

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: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
 
W

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
 
P

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top