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

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
>

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
>
>

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

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

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

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

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

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),counta($1:$1))"
>[quoted text clipped - 13 lines]
>> >>
>> >> Thanks

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