Announcement

Collapse
No announcement yet.

[Solved] VBA : Formatting Column Widths

Collapse
This topic is closed.
X
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • [Solved] VBA : Formatting Column Widths



    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

  • #2
    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 > m Then m = .Columns(j).ColumnWidth
            Next j
            .ColumnWidth = m
        End With
    End Sub
    Please do not U2U (private message) me directly for questions that should be posted to the forum; any such U2Us will be ignored.

    Comment


    • #3
      That is absolutely perfect!

      Thanks very much.

      Comment


      • #4
        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

        Comment


        • #5


          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.
          Please do not U2U (private message) me directly for questions that should be posted to the forum; any such U2Us will be ignored.

          Comment

          Working...
          X