PDA

View Full Version : If Range is non-Existent Then...



malik641
10-08-2005, 10:39 AM
What would be the proper code to write something like:

If Range("ThisRange") is non-Existent Then
MsgBox "Range doesn't Exist"
Exit Sub
End If


I've tried

If IsError(Range("ThisRange")) Then
MsgBox "Range doesn't Exist"
Exit Sub
End If

'I've also tried

If IsMissing(Range("ThisRange")) Then
MsgBox "Range doesn't Exist"
Exit Sub
End If
But I've had no luck with this http://vbaexpress.com/forum/images/smilies/090.gif

Any ideas??

Bob Phillips
10-08-2005, 10:46 AM
Function RangeExists(rng As String)
Dim rngTemp As Range
On Error Resume Next
Set rngTemp = Range(rng)
RangeExists = Not rngTemp Is Nothing
End Function

Sub TestRangeExists()

MsgBox RangeExists("Bob")

Range("A1:A10").Name = "Bob"
MsgBox RangeExists("Bob")

End Sub

malik641
10-09-2005, 07:44 AM
Great function xld, works just like I needed :thumb

Thanks!

Cyberdude
10-09-2005, 11:42 AM
May I proffer an unclassy alternative: Function RngExists(Rng As String) As Boolean
On Error Resume Next
Range(Rng).Select
RngExists = Err ? 1004
End Function

Sub RngExistsTst()
MsgBox RngExists("MyRange")
MsgBox RngExists("Phfft")
End Sub