Deleting #DIV/0! error

[abberyfarm]

Hi there,

I use this code to delete #DIV/0! errors in my worksheet and it works fine

Sub DeleteErrors()
    ActiveSheet.UsedRange.SpecialCells(xlCellTypeConstants, 16).ClearContents
End Sub

However it will not work for column AX which as been calculated using a formula (= P/M).

Any ideas on how I can get it to work? I've tried this code but it will not work either

Sub Deleteax()
Worksheets("kinematic").Range("ax:ax").SpecialCells(xlCellTypeConstants, 16).ClearContents
End Sub

Thanks

 

[machopicho]

Try this:

Sub test()
If IsError(Range("A1").Value) Then
    errval = Range("A1").Value
    Select Case errval
        Case CVErr(xlErrDiv0)
          Range("A1").ClearContents
    End Select
End If
End Sub

 

[Richard Schollar]

The first argument in the SpecialCells method should be set to Formulas:

Worksheets("kinematic").Range("ax:ax").SpecialCells(xlCellTypeFormulas, 16).ClearContents

 

[abberyfarm]

Hi I used this code, for column AX, but it didn't work. Does this only check cell ax1, the errors can occur on any row in the column, it varies from time to time so I need to check the entire column.

Thanks

Sub test()
If IsError(Range("Ax1").Value) Then
    errval = Range("Ax1").Value
    Select Case errval
        Case CVErr(xlErrDiv0)
          Range("Ax1").ClearContents
    End Select
End If
End Sub

 

[abberyfarm]

Hi richard, I tried that but it does not work

 

[Richard Schollar]

Works for me - do you definitely have some formula based errors in column AX in the specified worksheet?

 

[abberyfarm]

Hi yes I do,

Here is the code for column ax

Lastrow = Cells.Find(What:="*", After:=[A1], SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
Range("ax2:ax" & Lastrow).FormulaR1C1 = "=RC[-34]/RC[-37]"

Thanks

 

[abberyfarm]

Hi sorry for wasting your time,

I was calling the function before I even got to the that line in code!

Need to take a break!