Let me show you how to create self adjusting columns in excel with a few lines of vba code.

Example,

You can also copy and paste cells and the columns resize automatically .

Example,

VBA code

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
Application.ScreenUpdating = False
For Each Value In Target.Columns
    Worksheets(Sh.Name).Columns(Value.Column).AutoFit
Next Value
Application.ScreenUpdating = True
End Sub

Where to copy code?

  1. Press Alt + F11
  2. Double click ThisWorkbook in Project Explorer
  3. Copy vba code
  4. Paste in code module

Download excel 2007 *xlsm file 

Auto resize columns.xslm