Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
getting the absolute range address from a dynamic named range | Excel Programming | |||
Dynamic Range with unused formula messing up x axis on dynamic graph | Charts and Charting in Excel | |||
copying dynamic range based on cell outside of range | Excel Programming | |||
select dynamic range with dynamic start point | Excel Programming | |||
Excel 2000 VBA - Set Print Range in dynamic range | Excel Programming |