ArtsAutosBooksBusinessEducationEntertainmentFamilyFashionFoodGamesGenderHealthHolidaysHomeHubPagesPersonal FinancePetsPoliticsReligionSportsTechnologyTravel

VBA: Set Column Width and AutoFit in Excel

Updated on August 22, 2012

If you begin actively using excel it will not be long before you need to change the width of a column to accommodate so it can accommodate a larger field.

Luckily for you excel makes make this task pretty easy.

Setting Column Width

Move you pointer directly between the header of the column you would like to enlarge and the column next to it.

Your point cursor should change to a line with arrows pointing in either direction. See below:

Changing Width of Column "B"

When you see the column width cursor you can hold the left mouse button and drag either direction to set the desired width of your column.

Using AutoFit

Excel has included a function that allows you to automatically set the width of a column to fit the longest field it contains.

To use AutoFit double click the left mouse button while column width cursor is displayed.

Setting Width In VBA

In visual basic row width is set in millimeters.


'set the width of column A to 10 millimeters

Columns("A").ColumnWidth=10

or

Columns(1).ColumnWidth=10


'set width of column containing cell "B2" to 10 millimeters

Range("B2").EntireColumn.ColumnWidth=10

or

Cells(2,2).EntireColumn.ColumnWidth=10

Using AutoFit in VBA

'Autofit width of column "C" to widest cell value

Columns("C").Autofit

Example Macro

The macro below build on our macro demonstrating changing fonts and background colors. The column is now automatically set to the correct width.

Sub test()
Range("A1") = "Fun"
Range("A1").Font.Size = 72
Range("A1").Interior.Color = RGB(59, 179, 73)
Range("A2") = "With"
Range("A2").Font.Size = 72
Range("A2").Font.Color = RGB(99, 179, 73)
Range("A3") = "Colors"
Range("A3").Font.Size = 72
Range("A3").Font.Color = RGB(99, 179, 73)
Range("A3").Interior.Color = RGB(0, 0, 0)
Columns("A").AutoFit
End Sub

The End Result

Ready to Proceed

Comments

    0 of 8192 characters used
    Post Comment

    • profile image

      QuickFix 4 years ago

      I was looking to automatically expand a column and using the macro with autofit worked.

    • profile image

      Rubina 3 years ago

      Created well! As an excel beginner, I must unfortunately start at 0, so I amvery grateful for any kind of workshop. I hope that I mastered the functions soon and practice already after viewing the following site http://www.excel-aid.com/excel-column-widthadaptin...

    • profile image

      mannu 2 years ago

      width is not happening

    Click to Rate This Article