title: Find where named ranges are being used in big workbook
body: <p>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.</p>
  
  <p>Here is my (updated) attempt (if it is not evident already, i am an amateur):</p>
  
  <pre><code>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
  </code></pre>