excel columns collection?

[Ron]

Just checking if there is such a collection in excel as a
columns collection. I want to loop through a columns
collection for formatting.

Pseudocode

Dim col As Column, cols As Columns
Set cols = Columns(A:J)
For each col in cols
col.width = 10
Next

Is there a way to do something like this?

Thanks,
Ron

 

[Dave Peterson]

There's a range object you can use:

dim Col as range
dim cols as range

set cols = range("a:j")
'I like that better than columns("a:j"), but it won't matter.

for each col in cols.columns
col.columnwidth = 10
next col

but why not just:

columns("a:j").columnwidth = 10

===
I changed it to .columnwidth, too.

 

[Ron]

I think I figured this out

Dim sht As WorkSheet, i As Integer, col As Variant
Set sht = Sheets("Sheet1")
For i = 1 to sht.Columns("A:J").Count
sht.Columns(i).ColumnWidth = 11.5
Next

'--or

For each col in Sht.Columns("A:J")
Debug.print col.ColumnWidth
Next

 

[Doug Glancy]

Ron,

This may be more specific than you want, but it works for the situation you
describe:

Range("A1:J1").ColumnWidth = 2

More generally, no, there is no columns collection. Instead define ranges
and then use the .column or .entirecolumn or similar properties of those
ranges, e.g.,:

Sub test()
Dim col As Range
For Each col In Range("A1:J1")
col.ColumnWidth = 2
Next
End Sub

hth,

Doug