Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 182
  • Last Modified:

NA Error

Hi,

Im looking for a little sub i can run that will find any #N/A Errors and clear the contents of those cells

The tab is called "Output"

Thanks
Seamus
0
Seamus2626
Asked:
Seamus2626
  • 2
  • 2
  • 2
2 Solutions
 
Rory ArchibaldCommented:
Do you just want #N/A errors, or all errors?
0
 
Seamus2626Author Commented:
All errors now you say it, thanks
0
 
Rory ArchibaldCommented:
Try this then:
Sub ClearErrors()
   Dim rng As Range
   On Error Resume Next
   Set rng = Sheets("Output").UsedRange.SpecialCells(xlCellTypeFormulas, xlErrors)
   If Not rng Is Nothing Then rng.ClearContents
   Set rng = Sheets("Output").UsedRange.SpecialCells(xlCellTypeConstants, xlErrors)
   If Not rng Is Nothing Then rng.ClearContents

End Sub

Open in new window

0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
StephenJRCommented:
Try this:
Sub x()

Dim r As Range

For Each r In Sheets("Output").UsedRange.SpecialCells(xlCellTypeFormulas, xlErrors)
    If r.Value = CVErr(xlErrNA) Then r.ClearContents
Next r

End Sub

Open in new window

0
 
StephenJRCommented:
OK, hadn't seen change to request.
0
 
Seamus2626Author Commented:
Thanks guys,

Cheers,
Seamus
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 2
  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now