Microsoft Office Tutorials and References
In Depth Information
Finding and Deleting Erroneously Named Ranges
' * * * * *¶
Function WithoutRegExp(ByVal Text As String) As String¶
'Variable declaration¶
Dim i As Long¶
For i = 1 To Len(Text)¶
If Mid$(Text, i, 1) Like "#" Then¶
WithoutRegExp = WithoutRegExp & Mid$(Text, i, 1)¶
End If¶
Next i¶
End Function¶
Finding and Deleting Erroneously Named Ranges
This procedure shows how to quickly find and delete all the names that contain
reference errors (#REF!).
Example file:
Scenario: When working with named ranges, it is likely that
eventually one or more of those names will contain an error.
For example, when deleting a row or column that points to a
named range, that named range formula changes to =#REF!.
This macro displays a list of all these named ranges, and
provides an option to delete them.
View the Appendix to learn how to store this procedure
in a Standard module.
Option Explicit¶
' * * * * *¶
Sub DeleteBadNames()¶
'Variable declaration¶
Dim Nm As Name¶
Dim Book As Workbook¶
Dim ActBook As Workbook¶
Dim DisplayList As Boolean¶
'Turn off screen updating¶
Application.ScreenUpdating = False¶
Set ActBook = ActiveWorkbook¶
Select Case MsgBox( _¶
Prompt:="To display the bad names, click 'Yes'." & vbCrLf & _¶
"To remove the bad names, click 'No'." & vbCrLf & _¶
"To exit, press 'Cancel'.", _¶
Buttons:=vbQuestion + vbYesNoCancel)¶
Search JabSto ::

Custom Search