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
.
|