Autofit Data Automatically in Columns in MS Excel

Here is a query of a website visitor received by email:
How can I make data fit in a column automatically in Microsoft Excel? We do a lot of data entry work in our organization that is shared. Now when a user enters data like a full name (first and last name) I would like the column width to change automatically. The user need not have to drag or double-click to auto-fit the data in the worksheet cells. Can this be done with an Excel formula?

Normally we enter data into Excel cells and when we wish to ‘fit’ the data into the worksheet columns we either drag the column ends or double-click to autofit the data. We can also use the ‘format’ item in the cells group of the home tab to ‘autofit column width’ – a lengthy and time consuming process.

In the automatic method you take the help of Excel VBA as follows:

Click on the developer tab
Select Visual Basic
In the Microsoft Visual basic for Applications Window that opens double-click on ‘ThisWorkbook’ item on the left window and in the workspace on the right select ‘workbook’ by clicking on the left drop-down arrow and from the right-drop-down arrow select ‘SheetChange’ as shown in the Excel training video.
Two lines of code are inserted by the Visual Basic Editor for you
Now enter the line: Columns().Autofit
The macro code will autofit the data you input in a cell and after you press ‘enter’

The macro code:

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
Columns().AutoFit
End Sub

In the Excel VBA code above we have not defined the column address and written only ‘Columns()’. This means that all columns will Autofit the data whenever you enter data in any cell in the worksheet or workbook. If you wish that the data only in a specific column should Autofit then you can write the code as ‘Columns(“A”).AutoFit’ to have the data Autofit in column A. You can also select multiple columns and have the data AutoFit automatically using ‘Columns(“A:E”).AutoFit” to Autofit the data in columns A, B, D, D and E.
You can even use column numerical values like ‘Columns(1).Autofit’ to Autofit data in column A. You would have noticed that the numerical value need not be placed in quotes.
Watch the Microsoft Excel training video for a complete demo:


8 thoughts on “Autofit Data Automatically in Columns in MS Excel

  1. Srenivasank

    Hi sir, Its really awesome, and also i need a code that ,

    How to protect the cell date & time value (it means, if once i enter the value it will not change without my permission, no one can make changes manually.)

    Reply
  2. Kristine

    Dear Sir,

    I have watched several of your videos on UTube and read through a lot of articles on your website. They are so very helpful and have help make many great improvements on my workbooks.

    The one thing I am currently struggling with is autofitting row height when the cells are merged. The code I have implemented for non-merged cells is working wonderfully on my workbook with the exception of any cell that is merged. I am working with trying to autofit row height for cells that might have rows and/or columns merged together. Would you be able to help?

    Sincere thanks,
    Kristine

    Reply
  3. Colleen

    My worksheet goes from Column A through K.

    Need Columns A, B, D, E, F only to AutoFit width.

    Here is the code I used:
    Private Sub Workbook_SheetsChange(ByVal SH As Object, ByVal Target As Range)
    ‘Columns(“a:f”).autofit’

    I really don’t want column C, or G:K to autofit.

    Please Help?

    Thanks in advance,
    Colleen

    Reply
    1. Ansar

      Private Sub Workbook_SheetsChange(ByVal SH As Object, ByVal Target As Range)

      columns(“a:b”).autofit
      columns(“d:f”).autofit

      i hope this will work.

      Reply

Leave a Reply

Your email address will not be published. Required fields are marked *