VBA删除单个范围的多个RangeNames [英] VBA to delete multiple RangeNames for single Range

问题描述

我已经搜索了几个小时的答案，不能再搜索了。已经看到很多关于从工作簿或工作表中删除所有范围名称的讨论。但是，我需要从单个范围中删除多个范围名称，同时在同一工作表和/或同一工作簿中为其他范围留下其他范围名称。可能代码看起来像这样吗？：

  Sub Delete_My_Named_Ranges（）
 Dim nName As Name 
 Dim wbk As Workbook 
 Dim Sht As Worksheet 
 Dim rgNm As Range，aCell As Range 
 
设置wbk =工作簿（testRgNmDelete.xlsm）
 Set Sht = wbk .Worksheets（Sheet1）
 
设置rgNm = Sht.Range（$ A $ 1）
 
对于每个nName在ThisWorkbook.Names 
设置aCell =范围（nName）
如果不相交（aCell，rgNm）不是然后
 nName.Delete 
结束如果
下一个
结束Sub 
 
  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 
 
设置wbSKUM =工作簿（XBS_SKU_Master 
设置ws = wbSKUM.Worksheets（SKUMaster）
设置wsLUL = wbSKUM.Worksheets（LookupLists）
设置wsLU = wbSKUM.Worksheets（Lookup）
设置rgPTL = wsLUL.Range（ProdTypeLookUp）
设置rgTable1 = ws.Range（Table1）
 sFormula == SUBSTITUTE（SUBSTITUTE（F2， _）， - ，）
 
 
'清除产品类型查找列表（列D）以确保没有数据
 wsLUL 。激活
范围（范围（F2），范围（F2）。结束（xlDown））。选择
 Selection.Cells.Value = vbNullString 
设置rgNm =选择
 
对于每个nName在ThisWorkbook.Names 
设置aCell =范围（nName）
如果不相交（aCell，rgNm）不是$ 
 nName.Delete 
结束如果
下一个
  
再次感谢！ / p> 
 
解决方案
在声明后，您没有设置 rgName  
 
 
 
这是我对你的问题的理解。
 
 
 
让我们说有一个范围 A1：A10 在 Sheet1 和单元格 A2 中有一个名称 NM1 ，单元格A5的名称为 NM2 ，单元格D10的名称为$ code> NM3  
 
 
 
你想要一个代码，删除范围内的名称 A1：A10  ie  NM1 和 NM2 而不是 NM3  
 
 
 
如果上述是你想要的，那么尝试这个
  Option Exp合法
 
子样本（）
 Dim rgName As Range，aCell As Range 
 Dim nName As Name 
 
设置rgName =表（Sheet1） .Range（A1：A10）
 
对于每个nName在ThisWorkbook.Names 
设置aCell =范围（nName）
如果不相交（aCell，rgName）不是nName.Delete 
下一个
 End Sub 
  
如果我误解了你的问题那么你可能想改写它？
 
 
 
  FOLLOWUP  
 
 
这是一个非常有用的站点，作为一个新的VBA用户，但我绝对不知道如何添加到这个网站，超出这些小笔记。请尝试想象Excel 2010中的名称管理器 - 在名称管理器中，有10个唯一的名称，全部为= Sheet1！$ A $ 1。我想要所有与Sheet1！$ A $ 1相关的名称被VBA代码删除。我不想在任何地方删除其他名称。 -  LostInData 3分钟前
 
 
根据您上述的评论，尝试这个
  Option Explicit 
 
 Sub Sample（）
 Dim nName As Name 
 
 For This nName In ThisWorkbook.Names 
如果nName.RefersTo == Sheet1！$ A $ 1然后nName.Delete 
下一个
 End Sub 
  
 
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!
解决方案
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


                        
这篇关于VBA删除单个范围的多个RangeNames的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！