If you're looking for a template approach...

My position is that dynamic ranges need to be 'relative' to a fixed
'fully-absolute' named range! So assuming a data table exists from A1
thru G10, and row1 contains headers...

Name: "_Hdr1"
RefersTo: "=$A$1"

Name: "_Hdr1Row"
RefersTo: "=$1:$1"

Name: "_Hdr1Col"
RefersTo: "=$A:$A"

Name: "rngDataList"
RefersTo: "=OFFSET(_Hdr1,0,0,COUNTA(_Hdr1Col),COUNTA(_Hdr1Ro w))
(Defines the entire xy table of data)

Name: "rngData"
RefersTo: "=OFFSET(_Hdr1,1,0,COUNTA(_Hdr1Col)-1,COUNTA(_Hdr1Row))"
(Defines the data xy only)

Name: "rngHdrs"
RefersTo: "=OFFSET(_Hdr1,0,0,1,COUNTA(_Hdr1Row))"
(Defines the headers xy only)

...and your code can access either of the dynamic ranges as needed. Note
that you can assign each range to its own variant...

Dim vRngData, vRngDataList, vRngHdrs, wksData As Worksheet

Set wksData = ThisWorkbook.Sheets("Data")
With wksData
vRngData = .Range("rngData")
vRngDataList = .Range("rngDataList")
vRngHdrs = .Range("rngHdrs")
End With 'wksData

...so code can directly ref values easier than parsing from the entire
table. You really only need vRngDataList since it holds all values code
would need access to, but some may find having the others more
'convenient' to work with. Personally, I prefer using just 1 var so
updating the worksheet, a listview, and/or a file can be handled more
efficiently. This way the data can be stored in a text file
("data.dat") and loaded into the worksheet and/or a userform for
editing with automated features. Obviously, you could also edit the
text file directly and all will be reflected next time you run your
project.

I normally do this manually when designing a project, but using code
sure would save some time/energy!<g (I do that with most stuff once I
get it nailed down!)

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion