While working on a complex Excel workbook that has several worksheets with lots of formulas, logic, and pivot tables, it can become a challenge to make changes without breaking something. The following VBA code examples help you find references across all worksheets in an Excel Workbook, making it easy to find affected rows and columns and make educated decisions.
The code examples shared in this blog are as below:
The code example below will help you find references for a specific table. First, find the table name in the "Table Design" menu in Excel.
Sub FindTableReferences()
Dim ws As Worksheet
Dim cell As Range
Dim tableRef As String
Dim found As Boolean
' Set the table name you are looking for
Dim tableName As String
tableName = "YourTableName"
For Each ws In ThisWorkbook.Worksheets
For Each cell In ws.UsedRange
If InStr(cell.Formula, tableName) > 0 Then
Debug.Print ws.Name & ":" & cell.Address & " - " & cell.Formula
found = True
End If
Next cell
Next ws
If Not found Then
MsgBox "No references found for table: " & tableName
End If
End Sub
The code example below will help you find references for a specific sheet.
Sub FindSheetReferences()
Dim ws As Worksheet
Dim cell As Range
Dim sheetRef As String
Dim found As Boolean
' Set the sheet name you are looking for
Dim sheetName As String
sheetName = "YourSheetName"
For Each ws In ThisWorkbook.Worksheets
For Each cell In ws.UsedRange
If InStr(cell.Formula, sheetName) > 0 Then
Debug.Print ws.Name & ":" & cell.Address & " - " & cell.Formula
found = True
End If
Next cell
Next ws
If Not found Then
MsgBox "No references found for sheet: " & sheetName
End If
End Sub
The code example below will help you find references for a specific column.
Sub FindColumnInTables()
Dim ws As Worksheet
Dim lo As ListObject
Dim found As Boolean
Dim columnName As String
' Set the column name you are searching for
columnName = "YourColumnNameHere"
For Each ws In ThisWorkbook.Worksheets
For Each lo In ws.ListObjects
For i = 1 To lo.ListColumns.Count
If lo.ListColumns(i).Name = columnName Then
Debug.Print ws.Name & ": Table " & lo.Name & ", Column " & columnName
found = True
End If
Next i
Next lo
Next ws
If Not found Then
MsgBox "No columns found with name: " & columnName
End If
End Sub
The code example below will help you find references to a specific text in a workbook.
Sub FindReferencesInWorkbook()
Dim ws As Worksheet
Dim cell As Range
Dim found As Boolean
Dim reference As String
Dim outputMessage As String
' Set the reference you are searching for
reference = "YourTextHere"
For Each ws In ThisWorkbook.Worksheets
For Each cell In ws.UsedRange
If InStr(cell.Formula, reference) > 0 Then
Debug.Print ws.Name & ":" & cell.Address & " - " & cell.Formula
found = True
End If
Next cell
Next ws
If Not found Then
MsgBox "No references found for: " & reference
End If
End Sub
We have covered four unique ways to make your life simpler while working in Excel. These functions can make editing your Excel very easy and foolproof. Even if you are trying to study an Excel workbook shared by someone else, or digging into an old Excel file to find references, the above functions can come in handy.