Average column width, instead of maximum.

[e561414]

Hello, forum.

I have a list of descriptions, they live in a column. Some of them are very short, others are really long, but most of them are somewhere in between. If I double click the column header's right edge, it automatically adjust the width to display the longest description according to the view, I know that.

Question: Is there a way to make excel detect the most efficient width?

What is efficient? probably a width that lets me see most descriptions. I guess that would be the average width.

Does this make sense?

 

[ShawnPCooke]

There's nothing built into Excel like that, unfortunately. Perhaps if you were to wrap text in the description column, then you could maintain a more moderate width while still seeing the full description?

 

[e561414]

Thank you for replying. What do you mean by "wrap"? is it the text adjustment function?

To clarify, I don't really care about seeing the longest descriptions. I like my descriptions with a constant row height, and if I use the text adjustment, some cells become half a screen in height and then I can't scroll properly because Excel has issues with that too, since it scrolls by row.

There's probably a macro for anything, but I haven't found one for either problem (Yup I can't code, yet).

 

[ShawnPCooke]

I'm not sure what version of Excel you have, but in 2007-2010 at least, you can find on the Home tab, in the Alignment pane, an option called "Wrap Text." This causes a long line of text to break at the end of one line and continue onto the next, while remaining in the same cell. The height of the rows is adjusted so that the entire description can be seen. But as you stated, you prefer a constant row height, so that won't work for you.

A macro could certainly adjust column widths for you. The most straightforward approach is to set a maximum width, so that no column will autofit to wider than that. You can use the code below; just adjust the value of varMaxColumnWidth to what you want your maximum width to be.

Public Sub AdjustColumnWidths()

    'This subroutine autofits the current workbook, then
    'adjusts long columns to a maximum value.
    
    'Declarations
    Dim wb As Workbook
    Dim ws As Worksheet
    Dim rng As Range
    Dim varMaxColumnWidth As Variant
    Dim intColumnCount As Integer
    Dim j As Integer
    
    'Error handling
    On Error GoTo Abort
    
    'Initialization
    Set ws = ActiveCell.Parent
    Set rng = ws.UsedRange
    intColumnCount = rng.Columns.Count
    
    
    varMaxColumnWidth = 30      'Change this value to suit yourself
    
    'Autofit columns
    rng.EntireColumn.AutoFit
    
    'Loop through each column
    For j = 1 To intColumnCount
    
        'Check to see if the width is greater than the maximum
        If rng.Columns(j).ColumnWidth > varMaxColumnWidth Then
            
            'If so, adjust it to the maximum
            rng.Columns(j).ColumnWidth = varMaxColumnWidth
            
        End If
    
    Next j

Abort:

    'Destroy object variables
    Set rng = Nothing
    Set ws = Nothing
    Set wb = Nothing

End Sub

 

[e561414]

Thank you very much for the effort, this information is very useful, but it doesn't do what I'm looking for.

Here's what I'd like the macro to do:

Based on a selection:
1. Measure the width of each cell in the column
2. Calculate the standard deviation of those widths
3. Use the result as the new width for the whole column

It would be useful to apply a quick format to my reports.

 

[ShawnPCooke]

The standard deviation measures variation from the average. So for example, if you have cells of width 3, 4, 4, 4, and 5, the standard deviation would be 0.707107, which is not going to be wide enough for anything.

Do you mean that you want the column width to be one standard deviation above the average? Citing the same example, the average width is 4, so one standard deviation above the average would be 4.707107.

If so, accomplishing this would be a fairly extensive operation. When you say that you want to measure the width of each cell in the column, what I assume you mean is that you want to measure the value of the column width if the column were autofitted to that cell. To do that, it would require the following

a) Create a new worksheet.
b) Copy-transpose the first 65,536 non-blank values in your source column into the first row of the worksheet.
c) Autofit those columns
d) Capture the column widths
e) Repeat as needed until all values have been captured.
f) Delete the new worksheet
g) Perform the average and std. dev. calculations.
h) Fit the source column.

Depending on the amount of data you have, this will be a time-intensive operation.

 

[e561414]

Depending on the amount of data you have, this will be a time-intensive operation.

If so, then it's pointless, it'd be best to keep the column with a fixed width. All right, man, it's been very educational. Thank you very much.

Have a nice one.