Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
dynamically referencing ranges with variables
I'm hung up on what should be a simple problem:
I have a spreadsheet that adds data to columns from column G on depending on the number of elements I have in an array (each array element heads up a column). Once the code is complete, I need to format the sheet and this is where I get into problems. For example, lets say the array had five elements in it, so my code creates data in columns G through K. if I want to set the column width of columns G-K to 5 I can either code it statically [Columns("G:K").ColumnWidth = 5] which eliminates any ability to dynamically react to my array size or I can write a quick loop: With Range("A1").CurrentRegion col = .Columns.Count End With for i = 7 to col Columns(i).ColumnWidth = 5 next i But is there a way to name my range dynamically with a single statement? Art |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
dynamically referencing ranges with variables
This will give you the last column with an entry in Row 1 of the activesheet.
It may also execute faster htan what you have. Dim aWS as excel.worksheet Dim lCol as long Dim myRange as excel.range set aWS = activesheet lCol = aws.cells(1,aws.columns.count).end(xltoleft).colum n Set myrange = aws.cells(1,7).resize(aws.rows.count,lcol - 7 + 1) myRange.columnwidth = 5 -- HTH, Barb Reinhardt "c1802362" wrote: I'm hung up on what should be a simple problem: I have a spreadsheet that adds data to columns from column G on depending on the number of elements I have in an array (each array element heads up a column). Once the code is complete, I need to format the sheet and this is where I get into problems. For example, lets say the array had five elements in it, so my code creates data in columns G through K. if I want to set the column width of columns G-K to 5 I can either code it statically [Columns("G:K").ColumnWidth = 5] which eliminates any ability to dynamically react to my array size or I can write a quick loop: With Range("A1").CurrentRegion col = .Columns.Count End With for i = 7 to col Columns(i).ColumnWidth = 5 next i But is there a way to name my range dynamically with a single statement? Art . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Dynamically declare and populate variables | Excel Programming | |||
Referencing a cell dynamically | Excel Worksheet Functions | |||
Dynamically create a formula in Sheet B referencing SheetA | Excel Programming | |||
Referencing labels dynamically. | Excel Programming | |||
Dynamically declaring variables with composite name | Excel Programming |