Add a Dynamic Name Range

[Fredriksson via OfficeKB.com]

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

 

[Bob Phillips]

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)

 

[Guest]

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]

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.

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

 

[Fredriksson via OfficeKB.com]

This is getting close but how the code know what name to call the offset

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

 

[Fredriksson via OfficeKB.com]

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

I tried this but it did not work

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

 

[Guest]

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),counta($1:$1))"

I tried this but it did not work

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

 

[Fredriksson via OfficeKB.com]

Thanks this iworking better

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),counta($1:$1))"

[quoted text clipped - 13 lines]