| Home |
| Search |
| Today's Posts |
|
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro to Create Name Ranges for variable lengths of Data
On Jun 16, 1:19*am, GS wrote:
prkhan56 submitted this idea : On Jun 16, 12:01*am, GS wrote: Corrections: * Cell A1="City", Cell B1="Town", Cell C1="State" * Define A1 as... * * Name="'Sheet name'!City_Hdr", * * RefersTo="=$A$1" * Define the list under "City_Hdr" as... * * Name="'Sheet name'!City", * * RefersTo="=OFFSET(City_Hdr,1,0,COUNTA($A:$A)-1,1) * * Define B1 as... * * Name="'Sheet name'!City_Hdr", * * RefersTo="=$B$1" * Define the list under "Town_Hdr" as... * * Name="'Sheet name'!Town", * * RefersTo="=OFFSET(Town_Hdr,1,0,COUNTA($B:$B)-1,1) * * Define C1 as... * * Name="'Sheet name'!State_Hdr", * * RefersTo="=$C$1" * Define the list under "State_Hdr" as... * * Name="'Sheet name'!State", * * RefersTo="=OFFSET(State_Hdr,1,0,COUNTA($C:$C)-1,1) -- Garry Free usenet access athttp://www.eternal-september.org ClassicVB Users Regroup! comp.lang.basic.visual.misc Hi Gary, Thanks for your suggestion. Claus has provided the code Ok, but note that Claus's very nice code doesn't make the ranges dynamic and so it must be run every time new data is added. Debra's suggestion plus my suggestion results in the ranges automatically adjusting for data being added or removed. -- Garry Free usenet access athttp://www.eternal-september.org ClassicVB Users Regroup! comp.lang.basic.visual.misc- Hide quoted text - - Show quoted text - Hi Gary, Thanks for your time and suggestion. |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Create List sourced from Multiple sheets of varying lengths | New Users to Excel | |||
| sorting ranges of various column lengths, lookup values in columns | Excel Worksheet Functions | |||
| Economical grouping of lengths out of much longer lengths | Excel Worksheet Functions | |||
| Counting variable ranges and auto-summing variable ranges | Excel Programming | |||
| Validation List with Variable Lengths & Invisible Empty Cells | Excel Programming | |||
![prkhan56[_2_] is offline](https://www.excelbanter.com/images/SultanThemeVB3R/statusicon/user_offline.gif)
Threaded Mode
