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
$ OK,上述代码适用于固定范围($ A:$ 1)。但是我需要将rgNm设置为一个变量而不是一个固定的范围。这里是一个例子,现在的错误在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
设置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
ieNM1
和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
inSheet1
and CellA2
has a nameNM1
and Cell A5 has a nameNM2
and cell D10 has a nameNM3
And you want a piece of code which deletes the Names in Range
A1:A10
i.eNM1
andNM2
and notNM3
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屋!