I have tried both of the methods that you have suggested but unfortuantely no
one works.

The one from Bob returns me Compile Error: Syntax error and it gets stuck
while the one.

and yours it works only partially, meaning that it deletes some rows but not
all and it deletes also the ones with the relevant information i want to keep.

any idea why?
thank you





"Sandy" wrote:

This one will work too...

Sub DeleteRow()
Dim MyCell As Range
On Error Resume Next
For Each MyCell In Range("B1:B10000")
If CVErr(MyCell) = CVErr(xlErrNA) Then
MyCell.EntireRow.Delete
End If
DoEvents
Next MyCell
End Sub

Sandy

massi wrote:
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