Updated date:

VBA: Set Column Width and AutoFit in Excel


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




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




Using AutoFit in VBA

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


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)
End Sub

The End Result


Ready to Proceed

In the next module we will learn to hide and unhide columns and row in Excel and Visual Basic.


mannu on March 29, 2015:

width is not happening

Rubina on April 02, 2014:

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...

QuickFix on July 12, 2013:

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

Related Articles