查找大型工作簿中使用的命名范围 [英] Find where named ranges are being used in big workbook
问题描述
我在工作簿中有一个594个命名范围的列表,其中有近20张表,每张表有大约200列的数据。我需要找出使用命名范围的位置,以便删除不相关的范围。我将一个名为范围的列表粘贴到工作表上,然后我尝试通过记录它们在公式中使用它们,然后在所有工作表和列中使用find方法。问题是尽管使用lookin xlformulas,它检索命名范围,即使它只是一个文本。
I have a list of 594 named ranges in a workbook with nearly 20 sheets, each sheet has about 200 columns of data. I need to find out where the named ranges are being used so as to remove irrelevant ones. I pasted a list of named ranges onto the sheet and then I tried to find if they were used in a formula by recording them, and then using the find method in all sheets and columns. The problem is despite using lookin xlformulas, it retrieves the named range even if it is just a text.
这是我的(更新)尝试(如果它不明显,我是业余):
Here is my (updated) attempt (if it is not evident already, i am an amateur):
Application.ScreenUpdating = False
Count = ActiveWorkbook.Sheets.Count
Sheets(Count).Activate
Dim locr(1 To 595)
Dim locc(1 To 595)
Dim locn(1 To 595)
Dim nam(1 To 595)
Dim rng As Range
Range("a1").Select
For X = 1 To 595 'populate array with named ranges
ActiveCell.Offset(1, 0).Select
nam(X) = ActiveCell.Value
Next X
For i = 1 To 595 'name loop
For j = 1 To (Count - 1) 'sheet loop
Sheets(j).Activate
On Error Resume Next
Set orange = Sheets(j).Cells.SpecialCells(xlCellTypeFormulas) 'limit range to cells that only contain formulas
On Error GoTo 20 'if no formulas in sheet, go to next sheet
If Not orange Is Nothing Then
Set rng = orange.Find(What:=nam(i), _
LookIn:=xlFormulas, _
LookAt:=xlPart, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False) 'find named range
If Not rng Is Nothing Then 'if named range found
Application.Goto rng, True 'go to cell where name range found and record address
locr(i) = ActiveCell.Row
locc(i) = ActiveCell.Column
locn(i) = ActiveSheet.Name
GoTo 10 'value found, go to next sheet
Else
End If
Else
End If
20 Next j
locr(i) = "" 'record empty since "rng" is empty
locr(i) = ""
locr(i) = ""
10 Next i
Sheets(Count).Activate
Range("c1").Select
b = 1
For a = 1 To 595 'populate addresses of named ranges
ActiveCell.Offset(b, 2).Value = locr(a)
ActiveCell.Offset(b, 1).Value = locc(a)
ActiveCell.Offset(b, 0).Value = locn(a)
b = b + 1
Next a
推荐答案
这是我能想到的一种方式。我会在两部分中解释一下。
Here is one way I can think of. I will explain this in 2 parts.
第1部分
有一个命名范围 Sid
。
这个单词 Sid
可以以任何一种形式出现,如下图所示。为什么以 =
开始?这已在 Part2
中解释。
This word Sid
can appear in any one of these forms as shown in the image below. Why does it start with =
? That has been explained in Part2
below.
=Sid '<~~ 1
="Sid" '<~~ 2
=XSid '<~~ 3
=SidX '<~~ 4
=_Sid '<~~ 5
=Sid_ '<~~ 6
=(Sid) '<~~ 7
任何其他的场景,我猜会是上面的一个子集。现在,在我们的案例中,唯一有效的查找是第一个,最后一个,因为我们正在寻找我们的命名范围。
Any other scenarios, I guess will be a subset of the above. Now out of these the only valid find in our case is the first one and the last one since we are looking for our named range.
所以这里是一个快速的功能检查单元格公式是否具有命名范围。我确定它可以更有效率
So here is a quick function to check if the cell formula has a named range or not. I am sure it can be made more efficient
Function isNamedRangePresent(rng As Range, s As String) As Boolean
Dim sFormula As String
Dim pos1 As Long, pos2 As Long, sLen As Long, i As Long
sFormula = rng.Formula: sLen = Len(sFormula)
pos2 = 1
Do
pos1 = InStr(pos2, sFormula, s) - 1
If pos1 < 1 Then Exit Do
isNamedRangePresent = True
For i = 65 To 90
'~~> A-Z before Sid for example XSid
If UCase(Mid(sFormula, pos1, 1)) = Chr(i) Then
isNamedRangePresent = False
Exit For
End If
Next i
'~~> Check for " for example "Sid
If isNamedRangePresent = True Then _
If UCase(Mid(sFormula, pos1, 1)) = Chr(34) Then isNamedRangePresent = False
'~~> Check for underscore for example _Sid
If isNamedRangePresent = True Then _
If UCase(Mid(sFormula, pos1, 1)) = Chr(95) Then isNamedRangePresent = False
pos2 = pos1 + Len(s) + 1
If pos2 <= sLen Then
For i = 65 To 90
'~~> A-Z after Sid for example SidX
If UCase(Mid(sFormula, pos2, 1)) = Chr(i) Then
isNamedRangePresent = False
Exit For
End If
Next i
'~~> "Sid
If isNamedRangePresent = True Then _
If UCase(Mid(sFormula, pos2, 1)) = Chr(34) Then isNamedRangePresent = False
'~~> _Sid
If isNamedRangePresent = True Then _
If UCase(Mid(sFormula, pos2, 1)) = Chr(95) Then isNamedRangePresent = False
End If
Loop
End Function
所以在第一个和最后一个例子中, Debug.Print isNamedRangePresent(Range D2),Sid)
将给你 True
看到这个
So in the first and the last case, Debug.Print isNamedRangePresent(Range("D2"), "Sid")
will give you True
See this
第2部分
现在来到 .Find
,我看到你在工作表中只搜索一次因为你可以有许多场景的单词 Sid
存在,你不能只有一个 .Find
必须使用 .FindNext
。请参阅这个关于如何使用的链接。我已经解释了,所以我不会在这里解释。
Now coming to the .Find
. I see that you are searching only once in the worksheet. Since you can have many scenarios of the word Sid
being present, you cannot just have one .Find
. You will have to use .FindNext
. See THIS link on how to use that. I have explained it there so I won't bother explaining that here.
我们可以使我们的 .Find $ c $通过仅搜索具有公式的单元格,c>更有效。为此,我们必须使用
.SpecialCells(xlCellTypeFormulas)
。这就解释了为什么我们在 PART1
中的例子中有=。 :)
We can make our .Find
more efficient by searching only those cells which has formulas. To do that we have to use .SpecialCells(xlCellTypeFormulas)
. This explains why we had "=" in our example in PART1
. :)
这是一个例子(PART1代码添加在底部)
Here is an example (PART1 Code added at the bottom)
Sub Sample()
Dim oRange As Range, aCell As Range, bCell As Range
Dim oSht As Worksheet
Dim strSearch As String, FoundAt As String
Set oSht = Worksheets("Sheet1")
'~~> Set your range where you need to find - Only Formula Cells
On Error Resume Next
Set oRange = oSht.Cells.SpecialCells(xlCellTypeFormulas)
On Error GoTo 0
If Not oRange Is Nothing Then
strSearch = "Sid"
Set aCell = oRange.Find(What:=strSearch, LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False)
If Not aCell Is Nothing Then
Set bCell = aCell
'~~> Check if the cell has named range
If isNamedRangePresent(aCell, strSearch) Then FoundAt = aCell.Address
Do
Set aCell = oRange.FindNext(After:=aCell)
If Not aCell Is Nothing Then
If aCell.Address = bCell.Address Then Exit Do
'~~> Check if the cell has named range
If isNamedRangePresent(aCell, strSearch) Then FoundAt = FoundAt & ", " & aCell.Address
Else
Exit Do
End If
Loop
Else
MsgBox SearchString & " not Found"
Exit Sub
End If
If FoundAt = "" Then
MsgBox "The Named Range was not found"
Else
MsgBox "The Named Range has been found these locations: " & FoundAt
End If
End If
End Sub
Function isNamedRangePresent(rng As Range, s As String) As Boolean
Dim sFormula As String
Dim pos1 As Long, pos2 As Long, sLen As Long, i As Long
sFormula = rng.Formula: sLen = Len(sFormula)
pos2 = 1
Do
pos1 = InStr(pos2, sFormula, s) - 1
If pos1 < 1 Then Exit Do
isNamedRangePresent = True
For i = 65 To 90
'~~> A-Z before Sid for example XSid
If UCase(Mid(sFormula, pos1, 1)) = Chr(i) Then
isNamedRangePresent = False
Exit For
End If
Next i
'~~> Check for " for example "Sid
If isNamedRangePresent = True Then _
If UCase(Mid(sFormula, pos1, 1)) = Chr(34) Then isNamedRangePresent = False
'~~> Check for underscore for example _Sid
If isNamedRangePresent = True Then _
If UCase(Mid(sFormula, pos1, 1)) = Chr(95) Then isNamedRangePresent = False
pos2 = pos1 + Len(s) + 1
If pos2 <= sLen Then
For i = 65 To 90
'~~> A-Z after Sid for example SidX
If UCase(Mid(sFormula, pos2, 1)) = Chr(i) Then
isNamedRangePresent = False
Exit For
End If
Next i
'~~> "Sid
If isNamedRangePresent = True Then _
If UCase(Mid(sFormula, pos2, 1)) = Chr(34) Then isNamedRangePresent = False
'~~> _Sid
If isNamedRangePresent = True Then _
If UCase(Mid(sFormula, pos2, 1)) = Chr(95) Then isNamedRangePresent = False
End If
Loop
End Function
输出
PHEW !!!
这篇关于查找大型工作簿中使用的命名范围的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!