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!