Changing column widthd

[Bill Ridgeway]

How can you configure Excel to automatically expand column width when
inputting information?

Thanks.

Bill Ridgeway

 

[Rick Rothstein \(MVP - VB\)]

I don't know if there is a setting for this (although you could always
highlight the column and click Format/Column/Autofit Selection or double
click the column header's right side column's gridline after making an
entry), but you can use this worksheet event macro to have the column width
automatically reset itself for the longest entry in column...

Private Sub Worksheet_Change(ByVal Target As Range)
ActiveSheet.Columns(Target.Column).AutoFit
End Sub

Rick

 

[Dave Peterson]

or just:

target.entirecolumn.autofit

I don't know if there is a setting for this (although you could always
highlight the column and click Format/Column/Autofit Selection or double
click the column header's right side column's gridline after making an
entry), but you can use this worksheet event macro to have the column width
automatically reset itself for the longest entry in column...

Private Sub Worksheet_Change(ByVal Target As Range)
ActiveSheet.Columns(Target.Column).AutoFit
End Sub

Rick

 

[Rick Rothstein \(MVP - VB\)]

Doh! But of course <imagine sound of hand slapping forehead>.

Rick

 

[Bill Ridgeway]

Thanks. When I asked "how can you configure Excel to automatically expand
column width when inputting information?" I meant so that the column width
changes automatically at the point in time of pressing <enter> after
inputting data? It does it sometimes so it can be done! I know about
<Format><Column>AutofitSelection> which, by contrast, is done manually after
inputting data.

Thanks..

Bill Ridgeway

 

[Rick Rothstein \(MVP - VB\)]

Thanks. When I asked "how can you configure Excel to automatically expand

column width when inputting information?" I meant so that the column width
changes automatically at the point in time of pressing <enter> after
inputting data?

I know that is what you wanted... and I gave you a macro solution to do
that... and Dave posted a more efficient code statement than the one I gave
you, but the process is still the same. Try this and see if it does what you
want. Go to the worksheet you want this functionality on and press Alt+F11.
Doing this will take you to the VBA macro editor and put you in the code
window for that worksheet. Copy/Paste the following into that code window...

Private Sub Worksheet_Change(ByVal Target As Range)
Target.EntireColumn.AutoFit
End Sub

Now, go back to the worksheet you did this for and type something longish
into a cell and press Enter... is that what you were looking for? If you
need this functionality restricted to certain columns (or rows or even
individual cells), or perhaps widened to work on every worksheet in a
workbook, let us know and we can adjust the code for you.

Rick

 

[Bill Ridgeway]

Thanks Rick. I have some spreadsheets in which column width is
automatically expanded when inputting information and some that don't. I,
therefore, thought this was a configuration issue than anything else. Any
suggestions please?

Regards.

Bill Ridgeway

 

[Dave Peterson]

If the columnwidth has been autofit (or not touched), then numeric data will
expand the columnwidth.

Text data won't change the columnwidth automatically.