[Solved] VBA : Formatting Column Widths

Important Notice

Please note that on 14th December 2023 users will experience an expected outage whilst we make upgrades to our network. We anticipate this process may take a couple of hours and so we apologise in advance for any inconvenience.

  • 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.


  • 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 Selection.EntireColumn

  • 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,


  • 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.

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!