Adjusting Width of Each Column. For Each oCol In [range].Columns 1

[Gavona]

This is a general purpose macro to adjust the width of columns. It worked well but now I am adding the code to set a minimum width. I don't understand why Dim oCol as column results in a type mimatch at the highlighted row.

'Adjust the Width
iWidth = 8 'Default minimum width
With Range(ActiveCell.Offset(1, 0), ActiveCell.SpecialCells(xlLastCell))
    iWidth = InputBox("Enter Minimum Width for your data columns " & .Columns.Address, _
        "GKMacros FormatHeaderRows", _
    iWidth) 'input box to ask for minimum width
    .Columns.AutoFit
    [red]For Each oCol In .Columns()[/red]
        If oCol.Width < iWidth Then .Width = iWidth
    Next oCol
End With

Gavin

 

[TonyJollans]

Because Columns() is a collection of Ranges; there isn't a Column object, the Column Property of a Range is the number of the column, not the column itself.

Enjoy,
Tony

------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.

I'm working (slowly) on my own website

 

[SkipVought]

Gavin,

Range(ActiveCell.Offset(1, 0), ActiveCell.SpecialCells(xlLastCell)).ColumnWidth = iWidth

I would not be using ActiveCell.

I might use

Usedrange.Entirecolumn.Width = iWidth

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[PHV]

Anyway, I'd replace this:
If oCol.Width < iWidth Then .Width = iWidth
with this:
If oCol.Width < iWidth Then [!]oCol[/!].Width = iWidth

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[Gavona]

PHV: Yes, of course you are correct but it didn't resolve the problem. In fact I was watching oCol.width in the watch window but I don't understand what the result (34.5) represents.

However, examining the oCol object in the watch window I realised that should be
If oCol.[red]Column[/red]Width < iWidth Then oCol.[red]Column[/red]Width = iWidth

Tony: So how come oCol.width is 34.5? Half a column??

Skip: I can't see an alternative to ActiveCell or Selection. I only want to adjust the width for the column containing the active cell and everything to the right. I don't want to adjust the width of Rowfield columns (because I often use this on pivot tables with Outline Layout.
I would agree that if I only used this for pivottables then I could use the data area.

Just to be clear my code is now working but I don't understand what oCol.width represents. (my code now includes Dim oCol as variant). As an example:
oCol.address = "$L$5:$L$11"
oCol.Width = 47.25
oCol.ColumnWidth = 8.29
then moves on to the next column. Seems to be a multiplier in the range 5.5 to 6.0

Gavin

 

[PHV]

.Width is measured in points.
.ColumnWidth is measured in characters

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[PHV]

Have a look here:

http://msdn.microsoft.com/en-us/library/aa214203(v=office.11).aspx

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[Gavona]

Thanks PHV, that explains it.

Gavin