VBA Error Handling Issue with Blank Cells

Question

This is a sample of a part of a table that I'm working with:

This table is generated from some other VBA code. I wrote a simple script to clear the #VALUE terms from the table after it's generated.

'Clean any value errors before charting

    With Worksheets("CG Raw Data")

    On Error Resume Next

    Range("A2:W2").End(xlDown).SpecialCells(xlCellTypeFormulas, xlErrors).ClearContents

    On Error GoTo 0

    End With

End Sub

Now the table looks like:

For some reason this code clears the entire column if it has any blanks and not just the #VALUE errors. I want it to just delete the #VALUE errors and keep all other cells. As far as I can tell the code should be doing that. What is the error?

Wouldn't it be easier to just wrap the formulas with =IFERROR()? — Comintern
– Comintern, Commented Oct 20, 2016 at 14:12
yup that would work - I was mostly just curious why the code was clearing the entire columns with blanks and how to avoid that for future reference — beeba
– beeba, Commented Oct 20, 2016 at 14:19
You could use the VBA function IsError instead. You would loop through all cells (with For each cell in Range("A2:W2").End(xlDown) --- Next cell) and check If IsError(cell) Then --- End If. This should not do anything for blank cells. — Fabian F
– Fabian F, Commented Oct 20, 2016 at 14:23
I am curious what is in those blank cells? Are they really blank? I tested this code against a range of my own and it worked perfectly. — Scott Holtzman
– Scott Holtzman, Commented Oct 20, 2016 at 14:31
It didn't clear [Bear] completely. Are the formulas in other columns somehow dependant on the formulas that are being deleted? — user6432984
– user6432984, Commented Oct 20, 2016 at 14:31

Nulled · Accepted Answer · 2016-10-20 14:37:17Z

3

My first recommendation would be to wrap the formulas with =IFERROR(,"").

Alternatively, something like this should work:

DIM rngCell as Range

For Each rngCell In Worksheets(1).Range("A1:W" & Range("A1").End(xlDown).Row)
    If IsError(rngCell) Then
        rngCell.Clear
    End If
Next rngCell

I'd imagine that the entire dataset is being cleared because you're checking the entire range and consiquently clearing it if any value contains an error.

I don't have rep to leave a comment.

answered Oct 20, 2016 at 14:37

Nulled

3021 silver badge9 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Gary's Student · Accepted Answer · 2016-10-20 15:07:47Z

0

Issue #1 - you are Withing a specific worksheet, but not qualifying the Range()
Issue #2 - You will clear errors other than #VALUE

How about:

Sub NoValue()
    Dim r As Range, N As Long

    With Worksheets("CG Raw Data")
        N = .Cells(Rows.Count, "W").End(xlUp).Row
        For Each r In .Range("A2:W" & N)
            If r.Text = "#VALUE!" Then r.ClearContents
        Next r
    End With
End Sub

answered Oct 20, 2016 at 15:07

Gary's Student

96.9k10 gold badges66 silver badges104 bronze badges

Collectives™ on Stack Overflow

VBA Error Handling Issue with Blank Cells

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related