dynamically referencing ranges with variables

[c1802362]

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

 

[Barb Reinhardt]

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

Set myrange = aws.cells(1,7).resize(aws.rows.count,lcol - 7 + 1)
myRange.columnwidth = 5