Tip: Other languages are Google-Translated. You can visit the English version of this link.
Log in
x
or
x
x
Register
x

or

How to auto fit column width in Excel?

In Excel, you can adjust the column width to fit the cell contents quickly by using the AutoFit Column Width feature. Sometimes, you would like the column width automatically update based on the longest text in that column, that is to say, when input some long text string in cell, the column width will be resized automatically to fit the contents’ length. Is this possible to deal with it in Excel?

Auto fit columns width with VBA code

Auto fit columns width with shortcut keys

Excel Productivity Tools

Office Tab: Bring powerful tabs to Office (include Excel), just like Chrome, Safari, Firefox and Internet Explorer. Save you half the time, and reduce thousands of mouse clicks for you. 30-day Unlimited Free Trial

Kutools for Excel: Save 70% of your time and solve 80% Excel problems for you. 300+ advanced features designed for 1500+ work scenario, make Excel much easy and increase productivity immediately.60-day Unlimited Free Trial


arrow blue right bubble Auto fit columns width with VBA code

It seems to make the column width automatically updated with the cell contents’ length is an interesting trick, you can using the following VBA code to finish it.

1. Hold down the ALT + F11 keys, and it opens the Microsoft Visual Basic for Applications window.

2. Then double click ThisWorkbook in the VBA Project objects in the left pane to open a blank module, and then copy and paste following VBA code into the Module:

VBA code: auto fit column with in workbook

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
'Update 20141127
Dim xCol, xoutCol
xCol = Target.Column
If xCol > 52 Then
xoutCol = Chr(Int((xCol - 1) / 52) + 64) & _
Chr(Int((xCol - 27) / 26) + 64) & _
Chr(Int((xCol - 27) Mod 26) + 65)
ElseIf xCol > 26 Then
xoutCol = Chr(Int((xCol - 1) / 26) + 64) & _
Chr(Int((xCol - 1) Mod 26) + 65)
Else
xoutCol = Chr(xCol + 64)
End If
Columns(xoutCol & ":" & xoutCol).AutoFit
End Sub

doc-autofit-column-width-1

3. Then save and close this code, and now, when you enter long text into a cell, the column width will adjust to fit the content automatically.


arrow blue right bubble Auto fit columns width with shortcut keys

If you have multiple columns with part of cell contents are hidden as following screenshot shown, you want to resize the column widths at once to fit their contents. The following shortcut keys may help you.

doc-autofit-column-width-1

1. Select the data range which you want to resize the column width.

2. Hold down Alt key and press H + O + I, and now your column widths have been auto fitted to their contents immediately. See screenshot:

doc-autofit-column-width-1

Note: If you are using Excel 2003 and earlier version, you need to use these shortcut keys: ALT + O + C + A.


Related articles:

How to auto fit row height of merged cells in Excel?

How to resize row height or column width to fit text in Excel?


Excel Productivity Tools

Ribbon of Excel (with Kutools for Excel installed)

300+ Advanced Features Increase Your Productivity by 70%, and Help You To Stand Out From Crowd!

Would you like to complete your daily work quickly and perfectly? Kutools For Excel brings 300+ cool and powerful advanced features (Combine workbooks, sum by color, split cell contents, convert date, and so on...) for 1500+ work scenarios, helps you solve 80% Excel problems.

  •  Deal with all complicated tasks in seconds, help to enhance your work ability, get success from the fierce competition, and never worry about being fired.
  •  Save a lot of work time, leave much time for you to love and care the family and enjoy a comfortable life now.
  •  Reduce thousands of keyboard and mouse clicks every day, relieve your tired eyes and hands, and give you a healthy body.
  •  Become an Excel expert in 3 minutes, and get admiring glance from your colleagues or friends.
  •  No longer need to remember any painful formulas and VBA codes, have a relaxing and pleasant mind, give you a thrill you've never had before.
  •  Spend only $39, but worth than $4000 training of others. Being used by 110,000 elites and 300+ well-known companies.
  •  60-day unlimited free trial. 60-day money back guarantee. Free upgrade and support for 2 years. Buy once, use forever.
  •  Change the way you work now, and give you a better life immediately!

Office Tab Brings Efficient And Handy Tabs to Office (include Excel), Just Like Chrome, Firefox, And New IE

  • Increases your productivity by 50% when viewing and editing multiple documents.
  • Reduce hundreds of mouse clicks for you every day, say goodbye to mouse hand.
  • Open and create documents in new tabs of same window, rather than in new windows.
  • Help you work faster and easily stand out from the crowd! One second to switch between dozens of open documents!

 

Say something here...
symbols left.
You are guest ( Sign Up? )
or post as a guest, but your post won't be published automatically.
Loading comment... The comment will be refreshed after 00:00.
  • To post as a guest, your comment is unpublished.
    Bakrudin · 8 months ago
    saya tertarik dengan code VB yang anda terapkan tentang Cara mengisi kolom excel dengan VBA, jika pertanyaannya begini Bagaimana cara mengisi kolom dan baris tertentu di excel dnegan VBA?
    jadi yang terisi adalah kolom dan barisnya secara otomatis? mohon jawabannya..