How to Autofit column when cells have Wrapped Text?

[nomail1983]

Using macro record mode, I created a macro that inserts a row, sets
the format alignment to Right with Wrap Text selected, then inserted
titles in each cell, some which include chr(10) to create multi-line
titles. The VBA looks like this:

Rows("1:1").Select : Selection.Insert Shift:=xlDown
With Selection
.HorizontalAlignment = xlRight : .VerticalAlignment = xlBottom
.WrapText = True : .Orientation = 0 : .AddIndent = False
.IndentLevel = 0 : .ShrinkToFit = False : .ReadingOrder =
xlContext
.MergeCells = False
End With
Range("A1").Select : ActiveCell.FormulaR1C1 = "Year"
Range("B1").Select : ActiveCell.FormulaR1C1 = "Your" & Chr(10) &
"Age"
[... etc ...]

Since some of the original columns are not wide enough, the text wraps
within the too-narrow limits of the cell.

I tried to correct that by manually highlighting all columns, then
clicking on Format Column Autofit Selection. That generated the
following VBA code:

Columns("A:N").Select : Selection.Columns.AutoFit

But that failed to widen the columns to accomodate the text up to the
line break [chr(10)].

I can widen the columns to accomodate the text up to the line break,
both manually and in VBA?

(Actually, if you tell me how to do it manually, I can learn the VBA
using macro record mode.)

 

[nomail1983]

I can widen the columns to accomodate the text up to the line break,
both manually and in VBA?

That should have read: "How can I widen ...?".

I have a workable solution. I select the columns, set the column
width to something "far too big", __then__ do the Autofit. In VBA:

Columns("A:O").Select
Selection.ColumnWidth = 30 : Selection.Columns.autoFit

Of course, 30 is arbitrary -- good enough for my purposes.

I'm still open to a better idea.

 

[guy]

how about setting the WrapText = FALSE, then Columns.Autofit ?