Prev: disable clipboard alerts
Next: copy rows in range if data in first cell
From: mattg on 19 Apr 2010 11:37
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

From: Wouter HM on 19 Apr 2010 14:41
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
From: Project Mangler on 19 Apr 2010 14:42
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

"mattg" <mattg(a)discussions.microsoft.com> wrote in message
news:3888F1D6-21BB-4230-BDA7-D9ADF143C881(a)microsoft.com...
> 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
>


 | 
Pages: 1
Prev: disable clipboard alerts
Next: copy rows in range if data in first cell