Excel VBA remove local named ranges within a set of data

Question

I have some VBA in excel that I have assigned to a button. I select a cell anywhere inside some data on an excel sheet (which is arranged such that there is a top row with header names in and data beneath these) and press this button and it creates local named ranges for each column (ie local to the sheet as opposed to a global named range covering the whole spreadsheet), with the name being the column heading.

So given some data in excel presented like this: example data

If I selected any cell within B3:E8 and pressed the button it would create the local named ranges: "Year", "Sales", "Profit", "Loss" which would cover B4:B8, C4:C8, D4:D8 & E4:E8 respectively.

Note: I'm not working with ListObjects, just data in excel.

The code is:

Sub NameRangeWithTop()
    Dim rng As Range, col, nm
   
    Set rng = Selection.CurrentRegion
    If rng.Rows.Count = 1 Then Exit Sub 'check have a usable area...
   
    For Each col In rng.Columns
        nm = Replace(col.Cells(1), " ", "_")
        ActiveSheet.Names.Add Name:=nm, _
           RefersTo:=col.Offset(1).Resize(col.Cells.Count - 1)
    Next col
End Sub

This is great, but if I change a column heading and then press this button again, there will then be two local named ranges for that column (one with the old header name, one with the new header name). So now I'm looking to try and get it so it overwrites any existing named ranges for these columns.

I think the best way of doing this would be to have a separate bit of VBA code which would cycle through all the columns in Selection.CurrentRegion and delete any local named ranges. In a similar way as to how this code works (then my button would just process both macros, one after the other).

I've tried the below but it is giving an error: "Compile error: Expected: Then or GoTo" on the ':=' of the 'If nm.RefersTo:=col.Offset(1).Resize(col.Cells.Count - 1) then nm.delete' line.

Can anyone help on this?

Sub DeleteNamedRangesInWorksheet()
    Dim rng As Range, col
    Dim nm As Name

    Set rng = Selection.CurrentRegion
    If rng.Rows.Count = 1 Then Exit Sub 'check have a usable area...
    
    For Each col In rng.Columns
    
    For Each nm In ActiveWorksheet.Names

        If nm.RefersTo:=col.Offset(1).Resize(col.Cells.Count - 1) then nm.delete

    Next nm
    
    Next col
 

End Sub

userDazd · Answer

So an issue you'll run into with having dim nm as name is that you need to reference the .name of the name, e.g., Set labeledRange = Range(namedLabel.Name) and labeledRange.Name.Delete.

I updated some of the dimensions to create a range using the namedLabel, then use Intersect to determine if any intersect with the Selection.

Example data:

Example code:

Sub removedNamedRangesIntersectingWithSelection()
    Dim namedLabel As Name
    For Each namedLabel In ActiveWorkbook.Names
        Dim labeledRange As Range:  Set labeledRange = Range(namedLabel.Name)
        If Not Intersect(Selection, labeledRange) Is Nothing Then labeledRange.Name.Delete
    Next namedLabel
End Sub

End result from running code:

Excel VBA remove local named ranges within a set of data

1 Answers