CODEWITHSUNDEEP
excelexcel-2010vba
LostInData
LostInData

Reputation: 1

VBA to delete multiple RangeNames for single Range

I have searched for an answer for hours, and can't search anymore. Have Seen lots of discussions about deleting all range names from a workbook or a worksheet. However, I need to delete multiple Range Names from a single Range while leaving other Range Names for other ranges on the same sheet and/or within the same workbook alone. Might the code look something like this?:

Sub Delete_My_Named_Ranges()
   Dim nName As Name
   Dim wbk As Workbook
   Dim Sht As Worksheet
   Dim rgNm As Range, aCell As Range

   Set wbk = Workbooks("testRgNmDelete.xlsm")
   Set Sht = wbk.Worksheets("Sheet1")

   Set rgNm = Sht.Range("$A$1")

        For Each nName In ThisWorkbook.Names
            Set aCell = Range(nName)
            If Not Intersect(aCell, rgNm) Is Nothing Then
                nName.Delete
            End If
         Next
  End Sub

OK, the above code works for a fixed range ("$A:$1"). But I need to be able to set rgNm as a variable instead of as a fixed range. here is an example, the error now is on the statement "Set aCell = Range(nName)."

Private Sub cboProductType_Change()


Dim wbSKUM As Workbook
Dim ws As Worksheet, wsLUL As Worksheet, wsLU As Worksheet
Dim rgPTL As Range, rgTable1 As Range, rgA1 As Range, rgA1LU As Range
Dim rgNm As Range, rgFormula As Range, aCell As Range
Dim sFormula As String
Dim nName As Name

Set wbSKUM = Workbooks("XBS_SKU_Master.xlsm")
Set ws = wbSKUM.Worksheets("SKUMaster")
Set wsLUL = wbSKUM.Worksheets("LookupLists")
Set wsLU = wbSKUM.Worksheets("Lookup")
Set rgPTL = wsLUL.Range("ProdTypeLookUp")
Set rgTable1 = ws.Range("Table1")
    sFormula = "=SUBSTITUTE(SUBSTITUTE(F2,"" "",""_""),""-"","""")"


 'clear Product Type Lookup List (Column D) to be sure no data remains
 wsLUL.Activate
 Range(Range("F2"), Range("F2").End(xlDown)).Select
 Selection.Cells.Value = vbNullString
    Set rgNm = Selection

    For Each nName In ThisWorkbook.Names
            Set aCell = Range(nName)
            If Not Intersect(aCell, rgNm) Is Nothing Then
                nName.Delete
            End If
    Next

Thanks again!

Upvotes: 0

Views: 2472

Answers (2)

Bernat
Bernat

Reputation: 1

I think there is something missing in the solution given above.

In order to refer to the range of a named range with the range object you must use the name property of your named range. Therefore, not Set aCell = Range(nName) but rather Set aCell = Range(nName.Name).

Like this:

Option Explicit

Sub Sample()
    Dim rgName As Range, aCell As Range
    Dim nName As Name

    Set rgName = Sheets("Sheet1").Range("A1:A10")

    For Each nName In ThisWorkbook.Names
        Set aCell = Range(nName.Name)
        If Not Intersect(aCell, rgName) Is Nothing Then nName.Delete
    Next
End Sub

Upvotes: 0

Siddharth Rout
Siddharth Rout

Reputation: 149335

You are getting that error becuase you have not set rgName after declaring it.

Here is my understanding of your question.

Lets say there is a Range A1:A10 in Sheet1 and Cell A2 has a name NM1 and Cell A5 has a name NM2 and cell D10 has a name NM3

And you want a piece of code which deletes the Names in Range A1:A10 i.e NM1 and NM2 and not NM3

If the above is what you want then try this

Option Explicit

Sub Sample()
    Dim rgName As Range, aCell As Range
    Dim nName As Name

    Set rgName = Sheets("Sheet1").Range("A1:A10")

    For Each nName In ThisWorkbook.Names
        Set aCell = Range(nName)
        If Not Intersect(aCell, rgName) Is Nothing Then nName.Delete
    Next
End Sub

And if I have misunderstood your question then you might want to rephrase it?

FOLLOWUP

This has been an extremely useful site to me as a new VBA user, but I have ABSOLUTELY no idea how to add to this site, beyond these little notes. Please try to imagine the Name Manager in Excel 2010 - in Name Manager there are say 10 unique names, all for =Sheet1!$A$1. I want all of these names which pertain to Sheet1!$A$1 to be deleted by VBA code. I DO NOT want other names to be deleted anywhere. – LostInData 3 mins ago

Based on your above comment try this

Option Explicit

Sub Sample()
    Dim nName As Name

    For Each nName In ThisWorkbook.Names
        If nName.RefersTo = "=Sheet1!$A$1" Then nName.Delete
    Next
End Sub

Upvotes: 1

Related Questions