Find where named ranges are used in the big book

Question

I have a list of 594 named ranges in a book with almost 20 sheets, each sheet contains about 200 columns of data. I need to find out where named ranges are used to remove irrelevant ones. I inserted a list of named ranges on a sheet, and then tried to find if they were used in the formula, writing them down and then using the find method in all sheets and columns. The problem is that despite using lookin xlformulas, it retrieves the named range, even if it's just text.

Here is my (updated) attempt (if this is no longer obvious, I'm 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 

Answer 1

Here is one way I can think of. I will explain this in two parts.

PART 1

Say we have a named range of Sid .

This word Sid can appear in any of these forms, as shown in the image below. Why does it start with = ? This is explained below in Part2 .

 =Sid '<~~ 1 ="Sid" '<~~ 2 =XSid '<~~ 3 =SidX '<~~ 4 =_Sid '<~~ 5 =Sid_ '<~~ 6 =(Sid) '<~~ 7 

Any other scenarios, I think, will be a subset of the above. Now, of these, the only valid find in our case is the first and last, since we are looking for our named range.

So here is a quick function to check if a cell formula has a named range or not. I am sure that this can be made more effective.

 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 '~~> AZ 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 '~~> AZ 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 

So, in the first and last case Debug.Print isNamedRangePresent(Range("D2"), "Sid") will give you True . See this

PART 2

Now go to .Find . I see that you are looking only once per sheet. Since you can have many scenarios for the presence of the word Sid , you cannot just have one .Find . You will need to use .FindNext . See this link for how to use it. I explained it there, so I will not explain it here.

We can make our .Find more efficient by searching only those cells that have formulas. For this we must use .SpecialCells(xlCellTypeFormulas) . This explains why in our example in PART1 we had "=". :)

Here is an example (PART1 code added below)

 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 '~~> AZ 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 '~~> AZ 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 

Exit

Phew !!!

Answer 2

This code creates a copy of the book with names. He then scans and deletes each name in the list of names from this copied book. It counts the number of formula errors in the book before and after. If the number of errors is the same, the name was not used. If it is different, the name was used.

I like to do such a test for really difficult situations like this. This means that you don’t need to worry so much about complex testing rules. You can simply base your answer on the results.

Since testing is performed on a copy, it must be safe. Be sure to save all your work before!

To use, put your list of names in the book and name the range with this list "NamesToTest":

Then put this code in the same book and run it:

 Sub CheckNameUsage() Dim WorkbookWithList As Excel.Workbook Dim WorkbookWithNames As Excel.Workbook Dim TempWb As Excel.Workbook Dim cell As Excel.Range Dim NameToCheck As String Dim ws As Excel.Worksheet Dim ErrorRange As Excel.Range Dim ErrorsBefore As Long Dim ErrorsAfter As Long Dim NameUsed As Boolean Set WorkbookWithList = ThisWorkbook Set WorkbookWithNames = Workbooks("SO - wb to test.xlsx") 'adjust to suit WorkbookWithNames.Worksheets.Copy 'Workbooks.Add(WorkbookWithNames.FullName) Set TempWb = ActiveWorkbook For Each cell In WorkbookWithList.Names("NamesToTest").RefersToRange.Cells NameToCheck = cell.Value ErrorsBefore = 0 For Each ws In TempWb.Worksheets Set ErrorRange = Nothing On Error Resume Next Set ErrorRange = ws.Cells.SpecialCells(xlCellTypeFormulas, 16) On Error GoTo 0 If Not ErrorRange Is Nothing Then ErrorsBefore = ErrorsBefore + ErrorRange.Cells.Count End If Next ws TempWb.Names(NameToCheck).Delete ErrorsAfter = 0 For Each ws In TempWb.Worksheets Set ErrorRange = Nothing On Error Resume Next Set ErrorRange = ws.Cells.SpecialCells(xlCellTypeFormulas, 16) On Error GoTo 0 If Not ErrorRange Is Nothing Then ErrorsAfter = ErrorsAfter + ErrorRange.Cells.Count End If Next ws NameUsed = True If ErrorsBefore = ErrorsAfter Then NameUsed = False End If Debug.Print NameToCheck; " - Errors Before = " & ErrorsBefore; ", Errors After = " & ErrorsAfter; ", Used = " & NameUsed; "" Next cell TempWb.Close False End Sub 

Results will be displayed in the debug window:

The code, we hope, is clear enough. SpecialCells is worth knowing, so read it if necessary. In this case, it identifies cells with errors - these are 16 arguments.

Note that this only checks the names of book names. If necessary, you can add level checks to the worksheet.

Answer 3

The following code works for me. Interesting points:

1) You can use the range.ShowDependents method to draw arrows for cells that depend on this range. When you're done, use range.ShowDependents True to remove the arrows.

2) After drawing the arrows range.NavigateArrow can follow these arrows and return the resulting range. I could not find documentation of what would happen if there are no dependent ranges. By experimenting, I was able to determine that it will return the original range if there are no dependents.

 Sub test_for_dependents(nm As Name) Dim nm_rng As Range, result As Range Dim i As Long Set nm_rng = nm.RefersToRange nm_rng.ShowDependents Set result = nm_rng.NavigateArrow(False, 1, 1) If result.Parent.Name = nm_rng.Parent.Name And result.Row = nm_rng.Row _ And result.Column = nm_rng.Column Then MsgBox "Named range """ & nm.Name & """ isn't used!" End If nm_rng.ShowDependents True Set nm_rng = Nothing Set result = Nothing End Sub Sub test_all_names() Dim nm As Name Dim sht As Worksheet For Each nm In ThisWorkbook.Names test_for_dependents nm Next nm For Each sht In ThisWorkbook.Sheets For Each nm In sht.Names test_for_dependents nm Next nm Next sht Set nm = Nothing Set sht = Nothing End Sub