Check if Range EXISTS
 

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.

Check if Range EXISTS
 

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

"KIM W" wrote:

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.

Check if Range EXISTS
 

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

--
Steve

"KIM W" wrote in message
...
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.

Check if Range EXISTS
 

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

"Barb Reinhardt" wrote:

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

"KIM W" wrote:

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.