Check if Range EXISTS

[KIM W]

How can I verify if a range exists on a worksheet?
I am looping through all worksheets retrieving worksheet name info from a
range, but I don't want to include worksheets without the specified named
range. THe same name is used on each worksheet with scope limikted to the
worksheet.

 

[Barb Reinhardt]

You could do something like this

Dim myRange as Excel.Range
Dim myWS as Excel.Worksheet

for each myWS in ActiveWorkbook.Worksheets
Set myRange = nothing
on error resume next
Set myRange = myWS.Range("YourRange")
On Error GoTo 0
If Not myRange is Nothing then
'Range exists
else
'Range does not exist
end if
next myWS

 

[AltaEgo]

Function NmdRngExists(sRngName) As Boolean
Dim wbNamw, rngTest
On Error Resume Next
wbName = ActiveWorkbook.Name
Set rngTest = ActiveSheet.Range(sRngName)

'If there is no error then the name exists.
If Err = 0 Then

'Set the function to TRUE & exit
NmdRngExists = True
Exit Function
End If

End Function


' test by replacing myRange with your named range's name
Sub NmdRngExistsTest()
Dim sh
For Each sh In Worksheets
sh.Activate
MsgBox NmdRngExists("myRange")
Next sh
End Sub

 

[KIM W]

Thank you-- this checking for range name worked just fine.
KIM W.