VBA Express : Excel - Set minimum column width for Autofit columns

Excel

Set minimum column width for Autofit columns

Ease of Use

Easy

Version tested with

2002, 2003

Submitted by:

geekgirlau

Description:

Use Autofit to set all columns wider if required, but not narrower than the preferred width for the column.

Discussion:

If you have headings with wrapped text, Excel has no hesitation in breaking a single word over several lines if the column is made narrower. This makes for an unattractive end result, particularly if you have laid out your worksheet for optimum presentation. This procedure assumes that you have used a template sheet to format your headings and set the minimum width for each column that leaves your headings intact. Once the data has been populated, the procedure will make the columns wider if the data requires this, but will not make it narrower than the width originally set for the column. Please note that the procedure does not cover populating the data - just setting the column widths. In this example it assumes that the column headings are in row 1.

Code:

instructions for use

			Option Explicit 
 
Sub SetColumns() 
     '~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
     ' Purpose:      Autofit columns but make sure not less than default minimum width
     ' Assumption:   The preferred minimum column width has been previously set, so the
     '               procedure should make the column wider if required, but not skinnier
     '~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
     
    Dim rng As Range ' cells in heading range
    Dim sngWidth As Single ' default width of column
     
     
    For Each rng In Range(Range("A1"), Range("IV1").End(xlToLeft)) 
         
        sngWidth = Columns(rng.Column).ColumnWidth 
         
        rng.EntireColumn.AutoFit 
         
         ' reset column if it is skinnier than the original width
        If Columns(rng.Column).ColumnWidth < sngWidth Then 
            Columns(rng.Column).ColumnWidth = sngWidth 
        End If 
    Next rng 
End Sub

How to use:

Open Excel
Open the VBE window by pressing [Alt-F11]
Select Insert | Module
Copy the code above and paste it into the module
Press [Alt-F4] to close the VBE window

Test the code:

Copy all data rows in the sheet "Data"
Paste the data into cell A2 of the "Template" sheet
Select Tools | Macro | Macros
Double-click on "SetColumns"
Check that all data is displayed correctly, and no headings have strange text wrapping
Select column D on the "Template" sheet
Select Format | Column | Width
Type "11" and press [Enter]
Select Tools | Macro | Macros
Double-click on "SetColumns"
Note the width of column D

Sample File:

Set Column Width.zip 8.84KB

Approved by mdmackillop

This entry has been viewed 145 times.