Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Resize Range for Column
I have 4 named ranges that relate to a column of data starting on row 1 going
to the end of the column data. The columns may resize periodically since rows of data will be added. How do I write code that will name the range taking into account that each of the columns may resize and the range, therefore, will need to resize. If I use "Current Region" it selects the whole table of data since the columns are contiguous. I only want the range to select a column of data. I am currently using the following code to name ranges...but it is not dynamic if the column resizes by adding another row. Range("A1").Select Range(Selection, Selection.End(xlDown)).Select ActiveWorkbook.Names.Add Name:="ID", RefersToR1C1:= _ "=Assignment!R1C1:R34C1" Range("B1").Select Range(Selection, Selection.End(xlDown)).Select ActiveWorkbook.Names.Add Name:="ZLow", RefersToR1C1:= _ "Assignment!R1C2:R34C2" Range("C1").Select Range(Selection, Selection.End(xlDown)).Select ActiveWorkbook.Names.Add Name:="ZHigh", RefersToR1C1:= _ "=Assignment!R1C3:R34C3" Range("D1").Select Range(Selection, Selection.End(xlDown)).Select ActiveWorkbook.Names.Add Name:="Dnt", RefersToR1C1:= _ "=Assignment!R1C4:R34C4" |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Resize Range for Column
Nice tip! Thanks!
"Don Guillett" wrote: Instead of doing with code why not use insertnamedefinename it IDtype in =offset($a$1,0,0,match(99999999,$a:$a),1) if col a has letters instead us "zzzzzzzzzz" then col b =offset(ID,0,1) etc Now, the ranges will be self-adjusting based on the last cell in col A -- Don Guillett Microsoft MVP Excel SalesAid Software "Luce" wrote in message ... I have 4 named ranges that relate to a column of data starting on row 1 going to the end of the column data. The columns may resize periodically since rows of data will be added. How do I write code that will name the range taking into account that each of the columns may resize and the range, therefore, will need to resize. If I use "Current Region" it selects the whole table of data since the columns are contiguous. I only want the range to select a column of data. I am currently using the following code to name ranges...but it is not dynamic if the column resizes by adding another row. Range("A1").Select Range(Selection, Selection.End(xlDown)).Select ActiveWorkbook.Names.Add Name:="ID", RefersToR1C1:= _ "=Assignment!R1C1:R34C1" Range("B1").Select Range(Selection, Selection.End(xlDown)).Select ActiveWorkbook.Names.Add Name:="ZLow", RefersToR1C1:= _ "Assignment!R1C2:R34C2" Range("C1").Select Range(Selection, Selection.End(xlDown)).Select ActiveWorkbook.Names.Add Name:="ZHigh", RefersToR1C1:= _ "=Assignment!R1C3:R34C3" Range("D1").Select Range(Selection, Selection.End(xlDown)).Select ActiveWorkbook.Names.Add Name:="Dnt", RefersToR1C1:= _ "=Assignment!R1C4:R34C4" |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Resize Range for Column
Glad to help
-- Don Guillett Microsoft MVP Excel SalesAid Software "Luce" wrote in message ... Nice tip! Thanks! "Don Guillett" wrote: Instead of doing with code why not use insertnamedefinename it IDtype in =offset($a$1,0,0,match(99999999,$a:$a),1) if col a has letters instead us "zzzzzzzzzz" then col b =offset(ID,0,1) etc Now, the ranges will be self-adjusting based on the last cell in col A -- Don Guillett Microsoft MVP Excel SalesAid Software "Luce" wrote in message ... I have 4 named ranges that relate to a column of data starting on row 1 going to the end of the column data. The columns may resize periodically since rows of data will be added. How do I write code that will name the range taking into account that each of the columns may resize and the range, therefore, will need to resize. If I use "Current Region" it selects the whole table of data since the columns are contiguous. I only want the range to select a column of data. I am currently using the following code to name ranges...but it is not dynamic if the column resizes by adding another row. Range("A1").Select Range(Selection, Selection.End(xlDown)).Select ActiveWorkbook.Names.Add Name:="ID", RefersToR1C1:= _ "=Assignment!R1C1:R34C1" Range("B1").Select Range(Selection, Selection.End(xlDown)).Select ActiveWorkbook.Names.Add Name:="ZLow", RefersToR1C1:= _ "Assignment!R1C2:R34C2" Range("C1").Select Range(Selection, Selection.End(xlDown)).Select ActiveWorkbook.Names.Add Name:="ZHigh", RefersToR1C1:= _ "=Assignment!R1C3:R34C3" Range("D1").Select Range(Selection, Selection.End(xlDown)).Select ActiveWorkbook.Names.Add Name:="Dnt", RefersToR1C1:= _ "=Assignment!R1C4:R34C4" |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Resize Range & Fill Column with Formula - Help | Excel Programming | |||
how do I resize a range of cells in a column in excel? | Excel Discussion (Misc queries) | |||
Getting Range to move 1 column left and resize by 1 | Excel Programming | |||
Range.Delete and Range.Resize.Name performance issues | Excel Programming | |||
Range Resize | Excel Programming |