This is a tough one : deleting names, but in a specific range in VBA

[MissMJ]

Hello,

I am trying to figure out how to delete names of ranges in a specific range, using vba code.

I tried this :

ActiveWorkbook.Worksheets("Sheet1").Range("e6:bx2000").Names.Delete

but it doesn't work. I googled it... got nothing

Please help

 

[JackDanIce]

Welcome to the board. This is a guess, but you try (test on a copy of your file first):

Sub DeleteNamedRanges()

Dim nm  As Excel.Names
Dim rng As Excel.Range

    Application.ScreenUpdating = False
    
    With Sheets("Sheet1")
        Set rng = .Range("E6:BX2000")
        
        For Each nm In activeworkbooks.Name
            If Intersect(rng, Range(nm.Name)) Then nm.Delete
        Next nm
                
    End With
    
    Set rng = Nothing
    
    Application.ScreenUpdating = True
    
End Sub

 

[JackDanIce]

Ignore, found an error in above code. Try:

Sub DeleteNamedRanges()

Dim nm  As Excel.Name
Dim rng As Excel.Range

    Application.ScreenUpdating = False
    
    With ThisWorkbook.Sheets("Sheet1")
        Set rng = .Range("A6:BX2000")

        For Each nm In ActiveWorkbook.Names
            If Not Intersect(rng, .Range(nm.RefersToRange.Address)) Is Nothing Then
                nm.Delete
            End If
        Next nm
                
    End With
    
    Set rng = Nothing
    
    Application.ScreenUpdating = True
    
End Sub

 

[MissMJ]

These are good ideas, but the problem is that the name of the range is given with the sheet name attached. I guess I have to use the SPLIT function and then put the name of the sheet in SHEETS().range(nm) ?

 

[MissMJ]

OK, I tried and it still doesn't work.

Anyone has an idea why?