Check if Range EXISTS

  • Thread starter Thread starter KIM W
  • Start date Start date
K

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.
 
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
 
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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Back
Top