Public Sub Tester02()
Dim Rng As Range, Rng1 As Range

For i = 10000 To 7 Step -1
If IsError(Cells(i,"B") Then
If Rng1 Is Nothing Then
Set Rng1 = Rows(i)
Else
Set Rng1 = Union(Rng1,Rows(i))
End If
end If
Next i

If Not Rng1 Is Nothing Then Rng1.Delete

End Sub


--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"massi" wrote in message
...
Hi,
I need a macro that check the values of a column (let's say B) which has
links to another sheet. in case the cell is equal to "#N/A" I want to
delete
the whole row.

I have a macro that works fine if the cell is empty (see below) :
'=======================
Public Sub Tester02()
Dim Rng As Range, Rng1 As Range

Set Rng = Range("b7:b10000") '<<====== from b7 to b10000

On Error Resume Next
Set Rng1 = Intersect(Rng, _
Columns("B:B").SpecialCells(xlBlanks))
On Error GoTo 0

If Not Rng1 Is Nothing Then Rng1.EntireRow.Delete

End Sub
'<<=======================




I have tried this one below but it doesn't work.
'=======================
Public Sub Delete_empty()

If IsError(.Cells(Lrow, "b").Value) Then
'Do nothing, This avoid a error if there is a error in the
cell
ElseIf .Cells(Lrow, "b").Value = "#N/A" Then
..Rows(Lrow).Delete
'This will delete each row with the Value "ron" in Column
A,
case sensitive.
End If



End Sub
'<<=======================

any suggestion?

thankx