Resize Range for Column in Workbook
Sponsor
Resize Range for Column in Workbook [Q]
I dont know how to resize range for column in excel workbook, i have four named ranges that relate to a column of data starting on row 1 going
to the end of the column data on my excel spreadsheet.
The columns on my excel spreadsheet 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 excel 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″
Resize Range for Column in Workbook [A]
Here’s excel tips solution for you to resize range for column in excel workbook :
Use insert > name > define > name it ID>type 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
Rate This Tips:
(No Ratings Yet)
Loading ...Incoming excel search terms
vba resize range,excel vba resize column,excel vba resize columns,excel vba resize range,vba resize column,excel resize function,resize excel spreadsheet,excel vba resize,vba resize,range resize,excel resize range,excel vba resize named range,vba range resize,excel range resize,excel resize named range,resize range vba,excel vba range resize,excel vba resize selection,vba resize columns,vba resize selection,how to resize excel spreadsheet,mss,range resize vba,,resize range excel,vba excel resize column,excel column range,excel visual basic resize column,resize function excel,resize named range excel,resize named range vba,resize range excel vba,excel macro resize column,excel macro resize range,excel offset resize,excel resize,excel sheet range resize,how to resize a spreadsheet,how to resize columns in excel,offset and resize in excel vba,resize function vba,resize range,resize spreadsheet,resize vba excel,vba excel resize,vba resize cell,column range,excel resize columns vba,excel resize selection,how to resize spreadsheet
Related Excel Tips
Comments
One Comment on Resize Range for Column in Workbook
-
Excel Spreadsheet Resize Range for Column in Workbook | HiTechBooks on
Wed, 12th Aug 2009 6:49 pm
[...] View original post here: Excel Spreadsheet Resize Range for Column in Workbook [...]
Have another excel answer or questions for this problem ?
Feel free to post it here..
Best Excel Tips
- Calling Macro Visual Basic Procedures of a Different Project
(5.00 out of 5) - How to Remove Excel Vba Macro Button (5.00 out of 5)
- Remove Hyperlinks in Excel (5.00 out of 5)
- Delete Worksheet (5.00 out of 5)
- Copy from Worksheet (5.00 out of 5)
- How to Calculate 6 Working Days Between Two Dates (5.00 out of 5)
- Unprotect Worksheet Password (5.00 out of 5)
- Calling Macro Visual Basic Procedures of a Different Project
