VBA Delete Cells That Contains #VALUE!

[unknownymous]

Hello Gurus,

How can you delete/clear those cells that contains #VALUE! (note that it contains formula).

So far I saw this code:
= = = =

Delete cell only
If IsError(Range("C7")) Then Range("C7").Delete

= = = =
I have 10-15 tabs sheets and I need to run the macro like scan cells that contains #VALUE! (no specific column/rows) and clear contents for specific sheets let's say Sheet 1, 3 & 5.

Appreciate the help.

 

[Herakles]

#VALUE is Excel's way of saying, "There's something wrong with the way your formula is typed. Or, there's something wrong with the cells you are referencing."

The formula is, hopefully, there for a good reason.

You really need to get to the bottom of what the real problem is.

It may then point you to other, less obvious, things that are going wrong.

 

[alansidman]

Option Explicit

Sub delerror()
    Dim ws As Worksheet
    Dim c As Range
    Dim rng As Range
    For Each ws In ThisWorkbook.Worksheets
        Set rng = ws.Range("A1").CurrentRegion
        For Each c In rng
            If IsError(c) Then c.ClearContents
        Next c
    Next ws
    MsgBox "Action completed"
End Sub

 

[unknownymous]

#VALUE is Excel's way of saying, "There's something wrong with the way your formula is typed. Or, there's something wrong with the cells you are referencing."

The formula is, hopefully, there for a good reason.

You really need to get to the bottom of what the real problem is.

It may then point you to other, less obvious, things that are going wrong.

For some reason I am getting an error value.

Column S8 - my formula is =$R8-$U8
Column T8 - my formula is =$R8-$T8

Probably, I should fix the formula like it there's an error wherein there's no data, it should be left as blank cell.

 

[Michael M]

=IF(OR($R8="",$U8=""),"",$R8-$U8)

 

[unknownymous]

=IF(OR($R8="",$U8=""),"",$R8-$U8)

Thank you!

 

[Michael M]

Glad to help...