How to auto-size column-width in Excel during text entry

I'm not sure if there is a way to do it while your typing. I think excel generally stretches the cell view to display all the text before it fires the worksheet_change event.

This code will resize the column after you have changed and moved the target to a new range. Place it in the worksheet module.

Private Sub Worksheet_Change(ByVal Target As Range)    Dim nextTarget As Range    Set nextTarget = Range(Selection.Address) 'store the next range the user selects    Target.Columns.Select 'autofit requires columns to be selected    Target.Columns.AutoFit    nextTarget.SelectEnd Sub

If your just looking to do it for a particular column you would need to check the target column like this:

Private Sub Worksheet_Change(ByVal Target As Range)    Dim nextTarget As Range    Set nextTarget = Range(Selection.Address) 'store the next range the user selects    If Target.Column = 1 Then        Target.Columns.Select 'autofit requires columns to be selected        Target.Columns.AutoFit        nextTarget.Select    End IfEnd Sub

I cannot think of a way to do what you ask for but something very close to your need.In modern versions of Excel (2010+, I don't know about the 2007 version) you could use the following macro to resize your column to fit data as soon you finished entering data in a cell.

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)    Application.ScreenUpdating = False    ActiveSheet.Columns.AutoFitEnd Sub

Put the macro in ThisWorkbook module

This will automatically fit columns width

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