How to Autofit Column Widths with VBA

April 6th, 2007

Somebody asked me for VBA code that will automatically adjust column widths to fit the cell contents. It's actually very simple and all you need is one function call. You simply need to call the AutoFit method.

Sub AutoFitAllColumns()
    ActiveSheet.UsedRange.Columns.AutoFit
End Sub

If you wanted to autofit the row heights, you'll need something like this

Sub AutoFitAllRows()
    ActiveSheet.UsedRange.Rows.AutoFit
End Sub

Just note that for autofit of rows to work, you'll need to have the "Wrap Text" setting turned on. To do that, you need to select the range you want to autofit and from the main menu, go to Format->Cells. Then in the Alignment tab, tick the checkbox for "Wrap Text".

Posted in Excel, How To, VBA | No Comments




No Comments yet »

RSS feed for comments on this post. TrackBack URI

Leave a comment



(C) by Virgilio Adriano. All rights reserved. Powered by WordPress.
Entries and comments feeds.
It took 0.259 seconds to load this page.