AutoFit issue

[nikki333]

Hi Folks

I'm trying to use the autofit method on a cell range using VBA, however, it seems to have no effect.

Consindering rows, the problem seems to be the same as if doing a double-click on the header between rows...sometimes it does autofit and other times not. So, I'd like to understand why it doesn't auto-adjust to the needed height at times, while it does work sometimes.

My guess is that once the row height in question has been set, there is no way back; and then the autofit method gets useless. but i'm hoping someone will tell me i'm wrong:)

[Logit]

.
"My guess is that once the row height in question has been set, there is no way back; and then the autofit method gets useless. but i'm hoping someone will tell me i'm wrong:)"

If I correctly understand your comment .... you are correct. Once a row or a column has been adjusted to fit the data within, the autofit command doesn't have an effect.

[nikki333]

Somehow the row must be flagged then as "adjusted". Is there a way to get it "unflagged" again?

In my case, moreover, the cell in question is a merged range of 4 cells in a row, with text wrapping set to true. Apparently autofit doesn't work on merged cells.

[Logit]

.
I replicated what you described.

Although I haven't found a workaround yet, I'll continue to search. Or perhaps someone else on the FORUM has a solution ?





Sub autofitwidth()
Dim i As Integer
Cells.EntireColumn.AutoFit
For i = 1 To ActiveSheet.UsedRange.Columns.Count
Columns(i).ColumnWidth = Columns(i).ColumnWidth
Next i


Worksheets("Sheet1").Range("A:A").EntireRow.AutoFit
End Sub

[nikki333]

I've found a workaround; it's not very elegant, but it works

- First I loop through the merged range (all in a single row) to get the columnwidth property of each of the cells
- Then I copy the merged range to a standard cell and set it's column width to the width of the merged range to get the correct row height (with text wrapping set to true)