Check for the existence of a named range on a specific worksheet of  the current  workbook

Andreas Hermle
Andreas Hermle used Ask the Experts™
on
Dear Experts:

I would  like to check ...

... for the existence of a workbook level name (MyDataRange) in the current workbook.
... if Found the macro will check on which worksheet the workbook level name exists:

(1) if the named range is NOT located on the worksheet named 'DATA',
... a msgbox will tell the user the name of the worksheet including the cell references and the ... the macro will afterwards DELETE the workbook level name (MyDataRange). Afterwards the macro is to exit.
(2) If the named range IS located on the worksheet named 'DATA' a msgbox will say so including specifying the cell references of the named range

... if the workbook level name 'MyDataRange' does NOT EXIST at all, a msgbox  is to say so and the macro is to exit.

Help is much appreciated. Thank you very much for your professional help in advance.

Regards, Andreas
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Mechanical Engineer
Most Valuable Expert 2013
Top Expert 2013
Commented:
Here is a macro that checks for your named range:

Sub RangeChecker()
Dim rg As Range
Dim ws As Worksheet
Dim sName As String
sName = "MyDataRange"
On Error Resume Next
Set rg = ActiveWorkbook.Names(sName).RefersToRange
On Error GoTo 0
If rg Is Nothing Then
    MsgBox sName & " was not found"
ElseIf UCase(rg.Worksheet.Name) = "DATA" Then
    MsgBox sName & " exists on worksheet DATA at " & rg.Address(False, False)
    'Do more stuff here
Else
    MsgBox sName & " found instead on worksheet " & rg.Worksheet.Name & " at " & rg.Address(False, False)
End If
End Sub

Open in new window


Brad
Andreas HermleTeam leader

Author

Commented:
Hi Brad:  

great job. Concise coding. That's exactly the way I wanted it. Thank you very much for your professional help. I really appreciate it.

Regards, Andreas
Andreas HermleTeam leader

Author

Commented:
Great job, great forum.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial