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,vba resize column,excel vba resize columns,excel resize function,resize excel spreadsheet,excel vba resize range,excel resize named range,excel resize range,excel vba resize named range,range resize,vba range resize,vba resize columns,how to resize excel spreadsheet,resize range vba,excel range resize,excel vba resize,excel vba resize selection,mss,excel vba range resize,vba resize selection,,excel column range,excel visual basic resize column,range resize vba,resize named range excel,resize named range vba,resize range excel,vba resize,excel offset resize,excel resize,excel sheet range resize,offset and resize in excel vba,resize function excel,resize range,resize range excel vba,resize spreadsheet,vba excel resize column,vba resize cell,column range,excel macro resize column,excel macro resize range,excel resize columns vba,how to resize a spreadsheet,how to resize columns in excel,how to resize spreadsheet,offset and resize in excel,resize column vba,resize excel sheet,resize function vba
Related Excel Tips
Comments
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)
- 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)
- Remove Hyperlinks in Excel (5.00 out of 5)
- Unprotect Worksheet Password (5.00 out of 5)
- Calling Macro Visual Basic Procedures of a Different Project
