Delete '_xlfn' named cells / range

[nitrammada]

Hello everyone,
I've searched through the forums and found some excellent code to delete hidden named cells and ranges but none the will delete named cells or ranges that start with an underscore eg '_xlfn'. I have 3 that I want to delete, namely _xlfn.IFERROR, _xlfn.SINGLE and _xlfn.XLOOKUP. They all refer to #NAME? Can anyone help me with some code to delete them?
Thanks in advance.
Adam

 

[AlphaFrog]

Sub Delete_xlfn()
    Dim Found As Range, counter As Long
    Application.Calculation = xlManual
    Application.ScreenUpdating = False
    Set Found = ActiveSheet.UsedRange.Find("_xlfn", , xlFormulas, xlPart, 1, 1, 0)
    Do Until Found Is Nothing
        Found.ClearContents
        Set Found = ActiveSheet.UsedRange.FindNext(After:=Found)
        counter = counter + 1
        DoEvents
    Loop
    Application.Calculation = xlAutomatic
    Application.ScreenUpdating = True
    MsgBox counter & " cells deleted.", vbInformation, "Delete '_xlsm'"
End Sub

 

[nitrammada]

Sub Delete_xlfn()
    Dim Found As Range, counter As Long
    Application.Calculation = xlManual
    Application.ScreenUpdating = False
    Set Found = ActiveSheet.UsedRange.Find("_xlfn", , xlFormulas, xlPart, 1, 1, 0)
    Do Until Found Is Nothing
        Found.ClearContents
        Set Found = ActiveSheet.UsedRange.FindNext(After:=Found)
        counter = counter + 1
        DoEvents
    Loop
    Application.Calculation = xlAutomatic
    Application.ScreenUpdating = True
    MsgBox counter & " cells deleted.", vbInformation, "Delete '_xlsm'"
End Sub

Hi AlphaFrog, thanks so much for your response.
I ran the code but I still have these 3 named cells. I'm not sure, but could it be that I have formulas on veryhidden sheets? The custom software I'm using writes the data to separate sheets and then makes them veryhidden, so could it be that the formulas or, named cells (I'm not sure what causes these to occur) are residing on the veryhidden sheets as opposed to the active sheet?

 

[Fluff]

Ultimately deleting those names will have no effect if you have formulae that use the functions. Once you save, close & reopen xl they will be back.
They are there to provide some level of backwards compatibility without destroying the formulae.

 

[nitrammada]

Ultimately deleting those names will have no effect if you have formulae that use the functions. Once you save, close & reopen xl they will be back.
They are there to provide some level of backwards compatibility without destroying the formulae.

OK, I understand, as you can tell, I wan't sure what they were for, and it makes sense what you are saying, thank you for explaining and thanks to AlphaFrog too as I have learnt how to delete formula that has them.
Cheers.
Adam

 

[Fluff]

Glad we could help & thanks for the feedback.