Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Trapping error with dynamic range
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? |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Trapping error with dynamic range
"ArthurJ" wrote in message
... 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Thanks Rob. (eom)
"Rob Bovey" wrote: "ArthurJ" wrote in message ... 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Trapping error with dynamic range
Rob (or anyone),
I don't understand this line: On Error GoTo 0 "Rob Bovey" wrote: "ArthurJ" wrote in message ... 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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Trapping error with dynamic range
' 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 -- Regards, Tom Ogilvy "ArthurJ" wrote in message ... Rob (or anyone), I don't understand this line: On Error GoTo 0 "Rob Bovey" wrote: "ArthurJ" wrote in message ... 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Error referencing linked dynamic range | Links and Linking in Excel | |||
Defind Dynamic named Range up to first error | Excel Worksheet Functions | |||
#value! error trying to create a simple dynamic named range | Excel Discussion (Misc queries) | |||
error trapping | Excel Programming | |||
Error trapping | Excel Programming |