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

column-width, excel, vba

Solution

I'm not sure if there is a way to do it while you're 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 you're 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

Problem

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?

Original source