How to autofit column width up to maximum size?

[omnipotentmero]

Hey all, I am looking for a way to automate my worksheet formatting, but I don't know enough about excel's VBA macros to write one myself. What I am looking to do is to autofit all column widths on a given worksheet, but to limit the maximum size to 50.

I often deal with 30+ columns that have a mix of relatively narrow data (date, times, IP address...) and wide data (long URLS, or block of text). I want to be able to shrink the narrow fields to their most efficient use of space, truncate everything else so it does not visually take up the entire screen.

Thanks in advance for your help.

 

[Ferdi24]

1. Press Alt+F11
2. Click on Insert > Module
3. In the new Window that should have opened copy this code:
'-------------------------------------------
Sub mAutoWidth()
Dim mCell As Range
Application.ScreenUpdating = False

For Each mCell In ActiveSheet.UsedRange.Rows(1).Cells
mCell.EntireColumn.AutoFit
If mCell.EntireColumn.ColumnWidth > 50 Then _
mCell.EntireColumn.ColumnWidth = 50
Next mCell

Application.ScreenUpdating = True
End Sub
'-------------------------------------------
4. Close the VBA editor with Alt+F4
5. Back in Excel use Alt + F8 to see the list of available Macros
6. Choose mAutoWidth and click Run

 

[omnipotentmero]

Thanks! I just tested it out and it appears to have worked perfectly. This will save me a lot of time.