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.

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.

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" <(E-Mail Removed)> wrote in message
news:3C283E20-3C47-4A75-BFEC-(E-Mail Removed)...
> 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.

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.