Trapping error with dynamic range

[ArthurJ]

I have a dynamic range on my worksheet that expands (or contracts) to include
the data in a column. I have VBA code that utilizes this dynamic range. But
if there is no data in the column, the code generates an error ... apparently
there is no range object at all in this case. Other than general error
trapping methods, is there a more direct way to test whether the dynamic
range exists?

 

[Rob Bovey]

I have a dynamic range on my worksheet that expands (or contracts) to
include
the data in a column. I have VBA code that utilizes this dynamic range.
But
if there is no data in the column, the code generates an error ...
apparently
there is no range object at all in this case. Other than general error
trapping methods, is there a more direct way to test whether the dynamic
range exists?

Hi Arthur,

Here's one way to do it:

Dim rngTest As Range

On Error Resume Next
Set rngTest = Sheet1.Range("MyDynamicRange")
On Error GoTo 0

If Not rngTest Is Nothing Then
''' There is data in the dynamic range.
End If

--
Rob Bovey, Excel MVP
Application Professionals
http://www.appspro.com/

* Take your Excel development skills to the next level.
* Professional Excel Development
http://www.appspro.com/Books/Books.htm

 

[ArthurJ]

Hi Arthur,

Here's one way to do it:

Dim rngTest As Range

On Error Resume Next
Set rngTest = Sheet1.Range("MyDynamicRange")
On Error GoTo 0

If Not rngTest Is Nothing Then
''' There is data in the dynamic range.
End If

--
Rob Bovey, Excel MVP
Application Professionals
http://www.appspro.com/

* Take your Excel development skills to the next level.
* Professional Excel Development
http://www.appspro.com/Books/Books.htm

 

[ArthurJ]

Rob (or anyone),

I don't understand this line:

Hi Arthur,

Here's one way to do it:

Dim rngTest As Range

On Error Resume Next
Set rngTest = Sheet1.Range("MyDynamicRange")
On Error GoTo 0

If Not rngTest Is Nothing Then
''' There is data in the dynamic range.
End If

--
Rob Bovey, Excel MVP
Application Professionals
http://www.appspro.com/

* Take your Excel development skills to the next level.
* Professional Excel Development
http://www.appspro.com/Books/Books.htm

 

[Tom Ogilvy]

' ignore any errors
On Error Resume Next
Set rngTest = Sheet1.Range("MyDynamicRange")
' go back to breaking on errors (reset error handling to normal)
On Error GoTo 0