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

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
Andreas HermleTeam leaderAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
byundtConnect With a Mentor 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
0
 
Andreas HermleTeam leaderAuthor 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
0
 
Andreas HermleTeam leaderAuthor Commented:
Great job, great forum.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.