[Solved] VBA : Formatting Column Widths

MisterT · Apr 7th 2004

Hi,

I would like to be able to autosize excel columns based on the max width required for a range of cells, rather than autosize them all.
i.e. all columns will be the same width - that of the cell with the most characters.

I have had some success in doing this in one worksheet, but would like to know a more general solution, which could be applied whenever I select a row or range of cells in a row.

My simple solution:
1) Find the maximum length of characters in the range ={max(len(A1:A99))}
2) set column width based on the above
3) Copy column width (paste special) based on the above.

If anybody has done this before (I can't believe they haven't) or knows of a simple vba solution, I'd love to hear from them.

Thanks in advance.

Mike

Anonymous · Apr 7th 2004

The code below automates the steps you describe for the usedrange. If you want to just apply it to certain columns, then replace the line
With ActiveSheet.UsedRange.Columns
with
With Selection.EntireColumn

Code

Sub SetColumns()
    Dim j As Integer, m As Integer
    With ActiveSheet.UsedRange.Columns
        .AutoFit
        m = .Columns(1).ColumnWidth
        For j = 1 To .Columns.Count
            If .Columns(j).ColumnWidth &gt; m Then m = .Columns(j).ColumnWidth
        Next j
        .ColumnWidth = m
    End With
End Sub

Display More

MisterT · Apr 7th 2004

That is absolutely perfect!

Thanks very much.

MisterT · May 5th 2004

I'm sorry to have left it for so long, but it is not quite perfect -
The code will find the max column width for the whole column, not just that of the cells selected. Is it possible to select a range (A1:G1) and pull the max for that?

This will be of great use in resizing tables.

Thanks very much,

Mike

Anonymous · May 5th 2004

The problem with looking for the max length of characters in a range and then using that to set the column width is that unles you are using a constant width font, the cell with the most characters may not be the cell needing the most width. M and W are much wider than i or l. You could have a macro copy the specified range to a dummy sheet, find the max column width via the previous macro, and then apply that width to the columns on the real sheet. Give that a try.

[Solved] VBA : Formatting Column Widths

Participate now!

Share