Add a Dynamic Name Range
 

Using This Workbook module, I need to set up Name Ranges everytime the Work
Book is Open but the size of the range may change between each opening and
the user may delete a range when open.

When I recorded a Name defining Macro, it produce this code.
ActiveWorkbook.Names.Add Name:="CompanyXrefRange", RefersToR1C1:= _
"='Xref Property'!R2C1:R3182C4"

When I resize the range in my code I use this code
CompanyXrefRange.Resize(CompanyData.Rows.Count + 1, CompanyData.Columns.Count)
Name = "CompanyXrefRange"

Can I combine the two concept above so when This Workbook is executed it will
coount the rows and the columns to create the appropriate size of the Range

Thanks

--
Message posted via http://www.officekb.com

Add a Dynamic Name Range
 

Try this

ActiveWorkbook.Cells(CompanyData.Rows.Count + 1,
CompanyData.Columns.Count).Name="CompanyXrefRange"

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

"Fredriksson via OfficeKB.com" <u27002@uwe wrote in message
news:70295e96df0ca@uwe...
Using This Workbook module, I need to set up Name Ranges everytime the
Work
Book is Open but the size of the range may change between each opening and
the user may delete a range when open.

When I recorded a Name defining Macro, it produce this code.
ActiveWorkbook.Names.Add Name:="CompanyXrefRange", RefersToR1C1:= _
"='Xref Property'!R2C1:R3182C4"

When I resize the range in my code I use this code
CompanyXrefRange.Resize(CompanyData.Rows.Count + 1,
CompanyData.Columns.Count)
Name = "CompanyXrefRange"

Can I combine the two concept above so when This Workbook is executed it
will
coount the rows and the columns to create the appropriate size of the
Range

Thanks

--
Message posted via http://www.officekb.com

Add a Dynamic Name Range
 

When you set up the named range you could do something like this:

=offset(Sheet1!$A$1,0,0,counta($A:$A),counta($1:$1 ))

It will dynamically expand and contract as needed. Let me know if that's
what you need.


"Fredriksson via OfficeKB.com" wrote:

Using This Workbook module, I need to set up Name Ranges everytime the Work
Book is Open but the size of the range may change between each opening and
the user may delete a range when open.

When I recorded a Name defining Macro, it produce this code.
ActiveWorkbook.Names.Add Name:="CompanyXrefRange", RefersToR1C1:= _
"='Xref Property'!R2C1:R3182C4"

When I resize the range in my code I use this code
CompanyXrefRange.Resize(CompanyData.Rows.Count + 1, CompanyData.Columns.Count)
.Name = "CompanyXrefRange"

Can I combine the two concept above so when This Workbook is executed it will
coount the rows and the columns to create the appropriate size of the Range

Thanks

--
Message posted via http://www.officekb.com

Add a Dynamic Name Range
 

How would I delare CompanyData if I do not have a range define? How would
the program know Which Worksheet Columns and Rows to use.

Bob Phillips wrote:
Try this

ActiveWorkbook.Cells(CompanyData.Rows.Count + 1,
CompanyData.Columns.Count).Name="CompanyXrefRange "

Using This Workbook module, I need to set up Name Ranges everytime the
Work
[quoted text clipped - 16 lines]

Thanks

--
Message posted via http://www.officekb.com

Add a Dynamic Name Range
 

This is getting close but how the code know what name to call the offset
Barb Reinhardt wrote:
When you set up the named range you could do something like this:

=offset(Sheet1!$A$1,0,0,counta($A:$A),counta($1:$ 1))

It will dynamically expand and contract as needed. Let me know if that's
what you need.

Using This Workbook module, I need to set up Name Ranges everytime the Work
Book is Open but the size of the range may change between each opening and
[quoted text clipped - 12 lines]

Thanks

--
Message posted via http://www.officekb.com

Add a Dynamic Name Range
 

ActiveWorkbook.Names.Add Name:="CompanyXrefRange", RefersToR1C1:= _
"=offset(XrefProperty!$A$1,0,0,counta($A:$A),count a($1:$1))"

I tried this but it did not work

Barb Reinhardt wrote:
When you set up the named range you could do something like this:

=offset(Sheet1!$A$1,0,0,counta($A:$A),counta($1:$ 1))

It will dynamically expand and contract as needed. Let me know if that's
what you need.

Using This Workbook module, I need to set up Name Ranges everytime the Work
Book is Open but the size of the range may change between each opening and
[quoted text clipped - 12 lines]

Thanks

--
Message posted via http://www.officekb.com

Add a Dynamic Name Range
 

Use INSERT - NAME - DEFINE

Enter CompanyXrefRange for your RangeName
Enter =offset ... for your "Refers To"

Then look at the Sheet and type CTRL G and type in the RangeName. If the
offset equation needs to be modified for length, height, begin ... come back
and let me know the problem and I can assist.


"Fredriksson via OfficeKB.com" wrote:

ActiveWorkbook.Names.Add Name:="CompanyXrefRange", RefersToR1C1:= _
"=offset(XrefProperty!$A$1,0,0,counta($A:$A),count a($1:$1))"

I tried this but it did not work

Barb Reinhardt wrote:
When you set up the named range you could do something like this:

=offset(Sheet1!$A$1,0,0,counta($A:$A),counta($1:$ 1))

It will dynamically expand and contract as needed. Let me know if that's
what you need.

Using This Workbook module, I need to set up Name Ranges everytime the Work
Book is Open but the size of the range may change between each opening and
[quoted text clipped - 12 lines]

Thanks

--
Message posted via http://www.officekb.com

Add a Dynamic Name Range
 

Thanks this iworking better

Barb Reinhardt wrote:
Use INSERT - NAME - DEFINE

Enter CompanyXrefRange for your RangeName
Enter =offset ... for your "Refers To"

Then look at the Sheet and type CTRL G and type in the RangeName. If the
offset equation needs to be modified for length, height, begin ... come back
and let me know the problem and I can assist.

ActiveWorkbook.Names.Add Name:="CompanyXrefRange", RefersToR1C1:= _
"=offset(XrefProperty!$A$1,0,0,counta($A:$A),count a($1:$1))"
[quoted text clipped - 13 lines]

Thanks

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...mming/200704/1