Loading
Ozgrid Excel Help & Best Practices Forums

Excel Video Tutorials / Excel Dashboards Reports



Results 1 to 5 of 5

Thread: [Solved] VBA : Formatting Column Widths

  1. #1
    Join Date
    21st August 2003
    Posts
    83
    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

    Excel Video Tutorials / Excel Dashboards Reports


  2. #2
    Join Date
    15th April 2003
    Location
    USA
    Posts
    7,392
    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
    VB:
    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 
    
    

    Excel Video Tutorials / Excel Dashboards Reports


  3. #3
    Join Date
    21st August 2003
    Posts
    83
    That is absolutely perfect!

    Thanks very much.

    Excel Video Tutorials / Excel Dashboards Reports


  4. #4
    Join Date
    21st August 2003
    Posts
    83
    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

    Excel Video Tutorials / Excel Dashboards Reports


  5. #5
    Join Date
    15th April 2003
    Location
    USA
    Posts
    7,392
    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.

    Excel Video Tutorials / Excel Dashboards Reports


Thread Information

Users Browsing this Thread

There are currently 2 users browsing this thread. (0 members and 2 guests)

Possible Answers

  1. Set Multiple Column Widths
    By chatguy in forum EXCEL HELP
    Replies: 5
    Last Post: February 10th, 2008, 02:51
  2. Different Cell Widths In Same Column
    By m4r1u5 in forum EXCEL HELP
    Replies: 3
    Last Post: July 28th, 2007, 02:49
  3. Replies: 1
    Last Post: October 12th, 2005, 09:24
  4. VBA : Column Widths
    By Chas49 in forum EXCEL HELP
    Replies: 6
    Last Post: May 3rd, 2004, 21:52
  5. [Solved] column widths
    By joemenichino in forum EXCEL HELP
    Replies: 1
    Last Post: July 21st, 2003, 21:12

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
porno