VBA: If Cell contains #N/A delete selected range

[NessPJ]

Hi guys,

I've been fiddling around with a routine where i like to look for #N/A values in a selected range and if it is found delete a range of Cells on that row.

So far i've tried searching for String and Error values but i'm probably doing something wrong:

LastRowATImpShtResult3 = Sheets(ATTargetSht).Range("BZ" & "65536").End(xlUp).Row
    
    Dim XResult As Error
    
   n = 0


   For n = LastRowATImpShtResult3 To 3 Step -1
        XResult = Range("CB" & n).Err
        If XResult = xlErrNA Then
        Range("BW" & n & ":CF" & n).Delete Shift:=xlUp
        End If
    Next n

Can anyone help me with this please?

 

[steve the fish]

Try this out:

With Sheets(ATTargetSht)
    LastRowATImpShtResult3 = .Cells(.Rows.Count, "BZ").End(xlUp).Row
    If LastRowATImpShtResult3 > 2 Then
        For n = LastRowATImpShtResult3 To 3 Step -1
            If IsError(.Range("CB" & n).Value) Then
                If .Range("CB" & n).Value = CVErr(xlErrNA) Then
                    .Range("BW" & n & ":CF" & n).Delete Shift:=xlUp
                End If
            End If
        Next n
    End If
End With

This presumes the deletion should happen on the same sheet as the error cells are found.

 

[James006]

Hello,

You could test following ...

If(IsError(Range("CB" & n).Value) Then

Hope this will help

 

[steve the fish]

Hello,

You could test following ...

If(IsError(Range("CB" & n).Value) Then

Hope this will help

That will work of course if the range to be searched is on the activesheet and it doesnt matter if the error is anything other than NA as per OP request.

 

[NessPJ]

Thanks for the help guys!

Both solutions work although the VB Editor told me this had a Syntax error until i changed it to:

If IsError(Range("CB" & n).Value) Then

 

[steve the fish]

Put a sheet qualifier on your range. You could cause yourself problems depending on when you run the macro.

 

[NessPJ]

Put a sheet qualifier on your range. You could cause yourself problems depending on when you run the macro.

I have, but thanks!

 

[James006]

Glad you could fix your problem ...