Solved: Dynamic Range: Re-post

[bob1122]

I'm getting the following error message:
Run-time error '1004':
Method 'Range of object'_Global failed
Any help appreciated!

Sub highlight090208()

colkount = Application.CountA(Range("1:1"))
MsgBox (colkount)

rowkount = Application.CountA(Range("A:A"))
MsgBox (rowkount)

Set myData = Range("A1").Resize(rowkount, colkount)
Range("myData").Select

End Sub

[Bob Phillips]

myData is a range, so you don't need to use Range with it



colkount = Application.CountA(Range("1:1"))
MsgBox (colkount)

rowkount = Application.CountA(Range("A:A"))
MsgBox (rowkount)

Set myData = Range("A1").Resize(rowkount, colkount)
myData.Select

[mikerickson]

Usualy "Dynamic Range" refers to a named range rather than a VB range argument.
If you want a Dynamic Named Range, VB is not needed, one does it by selecting Insert>Name>Define from the menu bar.

Name: myDynamicData
RefersTo: =OFFSET(Sheet1!$A$1,0,0,COUNTA(Sheet1!$A:$A),COUNTA(Sheet1!$1:$1))

The reason it's called "dynamic" is that the user adding data in A:A or 1:1 will change the size of the array AFTER its been defined.

With arguments, adding data after the argument has been defined will not change the size of the range argument.

Dynamic named ranges can be refered to in VB routines as you attempted in the OP, MsgBox Range("myDynamicData").Address

[bob1122]

Thanks very much!