Clearing Cells with Errors

[mlarson]

Hi all, I am trying to create a macro that will clear the cells (not the entire row) that contain any error (#N/A, #VALUE!, etc.). I found information on deleting the entire row but nothing when searching for how to clear only the cells.

I would like to clear the cells where errors occur in Columns N:T. Can use workbook name of "Workbook1.xlsm"

Thanks for your help.

 

[shg]

    On Error Resume Next
    Range("N:T").SpecialCells(xlCellTypeFormulas, xlErrors).ClearContents

Can use workbook name of "Workbook1.xlsm"

To operate on something other than the active sheet, you need a worksheet reference, not a workbook reference.

 

[mlarson]

This is what I tried and it didn't clear #N/A nor #VALUE!, but the macro seemed to run and no pop-up error message appeared. Any thoughts on that?

Sub ClearCells()
Windows("Workbook1.xlsm").Activate
Sheets("Sheet1").Select
On Error Resume Next
Range("N:T").SpecialCells(xlCellTypeFormulas, xlErrors).ClearContents
End Sub

 

[shg]

Do those cells contain formulas?

EDIT: The On Error Resume Next statement suppresses the run-time error that would occur if there were no cells with formulas that returned errors.

 

[mlarson]

No, all the values have been made "value only." The numbers in the cells were calculated with a formula in a previous step but were also changed to value only prior to this step of trying to remove errors.

 

[shg]

    On Error Resume Next
    Workbooks("Workbook1.xlsm").Worksheets("Sheet1").Range("N:T").SpecialCells(xlCellTypeConstants, xlErrors).ClearContents

 

[mlarson]

Thanks, shg, it works very well! Appreciate it. And I don't know if you're a baseball fan but I see you're from Texas. I'm rooting for the Rangers to win the World Series as I'm an AL fan (Boston).

 

[shg]

Glad it helped.

I was in STL yesterday, explaining to all that the first game was a fluke. Last night was much better. And, coincidentally, the Boyz play STL on Sunday. Wouldn't a three-game sweep (Saturday night, Sunday afternoon, Sunday night) be a hoot?

 

[mlarson]

That would be pretty amazing. But I'm afraid I have to root against the Dallas defense as my fantasy football opponent has them this week But I fully expect them to crush the Rams. I think you're going to really enjoy your weekend with the Rangers playing at home and the Boyz picking apart Bradford & Co. Thanks again for your help on this!

 

[shg]

Fantasy football has ruined the sport ...