Ignore this go with Bernie's !

- wasn't thinking ):-

Regards,
Peter T

"Peter T" <peter_t@discussions wrote in message
...
Dim rng As Range, ar As Range
On Error Resume Next
Set rng = Range("A2:W" & Range("A1").End(xlDown).Row). _
SpecialCells(xlCellTypeFormulas, xlErrors).EntireRow
On Error GoTo 0

If Not rng Is Nothing Then

For Each ar In rng.Areas
' for testing change colour
ar.Columns(1).Interior.ColorIndex = 6
'ar.Clear
' or
'ar.ClearContents
Next
Else
MsgBox "no cells found"
End If


Uncomment Clear or ClearContents to "delete" them, if that's all you want
to
do. However if you need to build a new range of all the 'relative' cells
in
col-a you could build a new range with Union of each 'ar'

Regards,
Peter T

wrote in message
...
Hi,

I have a piece of code that puts an error "NA()" in column W if the ID
in column A is not found on a reference worksheet ...

like this:
Range("W2:W" & Range("A1").End(xlDown).Row).Formula =
"=IF(COUNTIF(A:A,Recon_Activity!T:T)=0,NA(),"" "")"

Normally, say I want to select the entire row of all the ones with an
error put in by the formula, I can use this:

Range("A2:W" &
Range("A1").End(xlDown).Row).SpecialCells(xlCellTy peFormulas,
xlErrors).EntireRow.Select

or I can in one step delete them all.

However, I would like to select the cells in column A where an error
value is found in column W ... much the same as above, but I would
instead of selectiing the entire row, how do I try and select the
cells in column A where there is an error on the same row in column
W...

I tried the below but it doesn't work:

Range("A2:W" &
Range("A1").End(xlDown).Row).SpecialCells(xlCellTy peFormulas,
xlErrors).Rows(0,1).select

thanks for any help
Philip