Changing column widthd

  • Thread starter Thread starter Bill Ridgeway
  • Start date Start date
B

Bill Ridgeway

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

Thanks.

Bill Ridgeway
 
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
 
or just:

target.entirecolumn.autofit



Rick Rothstein (MVP - VB) said:
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
Click to expand...
 
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
 
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?
Click to expand...

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
 
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
 
If the columnwidth has been autofit (or not touched), then numeric data will
expand the columnwidth.

Text data won't change the columnwidth automatically.
 
Back
Top