Macro Clear all items containing #N/A

[howard]

I would like a macro to clear all items in Col E containing #N/A

your assistance in this regard is most appreciated

 

[sky1in5]

sub test()
Dim LastRow,i as Long
LastRow = activesheet.range("E" & rows.count).end(xlup).row
for i = 1 to lastrow
if iserror(activesheet.range("E" & i)) = true then
activesheet.range("E" & i).clear
end if
next
end sub

 

[Rick Rothstein]

I would like a macro to clear all items in Col E containing #N/A

There may be a fast, non-looping way to do this, but a couple of questions first...

1) These #N/A values in Column E are all generated by formulas, correct?

2) Can there ever be any other errors in Column E besides #N/A errors?

 

[howard]

Thanks for the help, much appreciated

Rick, with regard to your question

1) These #N/A values in Column E are all generated by formulas

2) There are never any other errors in Column E besides #N/A errors


Regards

Howard

 

[kevatarvind]

simple way just replace using macro try below code

Sub Replace()

    Columns("E:E").Select
    Selection.Replace What:="#N/A", Replacement:="", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
    
End Sub

 

[VoG]

Or

Sub test()
Columns("E").SpecialCells(xlCellTypeFormulas, xlErrors).ClearContents
End Sub

 

[Rick Rothstein]

Or

Sub test()
Columns("E").SpecialCells(xlCellTypeFormulas, xlErrors).ClearContents
End Sub

Yep... that's the one I was aiming for with my questions.

 

[nuked]

Or you could add error handlers to the formulas, if you want to maintain the spreadsheet structure for future data.

Option Explicit
Sub AddIFERROR()
    Application.ScreenUpdating = False
    Application.Calculation = xlCalculationManual
    Dim xCell As Range
    Dim xFormula As String
    For Each xCell In Selection
        If xCell.HasFormula Then
            xFormula = Right(xCell.Formula, Len(xCell.Formula) - 1)
            xCell.Formula = "=IFERROR(" & xFormula & ","""")"
        End If
    Next xCell
    Application.Calculation = xlCalculationAutomatic
    Application.ScreenUpdating = True
End Sub

 

[howard]

Hi Guys

Thanks for all the input, much appreciated