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

Question

I usually try to avoid VBA in Excel, but it would be convenient to be able to type text into a cell, and have its column get wider or narrower to accommodate the text remaining as it's entered or deleted.

This would be subject, of course, to the lengths of the text in the other cells in the column.

'Auto-fit as you type', I guess you might call it.

Is there an easy way to do this in a suitable handler?

Answer 1

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.Select
End 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 If
End Sub