Changing column widthd

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Bill Ridgeway - 14 Dec 2007 20:14 GMT

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

Thanks.

Bill Ridgeway

Reply to this Message

Rick Rothstein (MVP - VB) - 14 Dec 2007 23:09 GMT

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

> How can you configure Excel to automatically expand column width when
> inputting information?
>
> Thanks.
>
> Bill Ridgeway

Reply to this Message

Dave Peterson - 14 Dec 2007 23:16 GMT

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
[quoted text clipped - 14 lines]
> >
> > Bill Ridgeway

Signature

Dave Peterson

Reply to this Message

Rick Rothstein (MVP - VB) - 15 Dec 2007 01:03 GMT

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

Rick

> or just:
>
[quoted text clipped - 19 lines]
>> >
>> > Bill Ridgeway

Reply to this Message

Bill Ridgeway - 15 Dec 2007 09:35 GMT

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

Reply to this Message

Rick Rothstein (MVP - VB) - 15 Dec 2007 10:46 GMT

> 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

Reply to this Message

Bill Ridgeway - 15 Dec 2007 15:10 GMT

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

>> Thanks. When I asked "how can you configure Excel to automatically
>> expand column width when inputting information?" I meant so that the
[quoted text clipped - 20 lines]
>
> Rick

Reply to this Message

Dave Peterson - 15 Dec 2007 15:47 GMT

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

Text data won't change the columnwidth automatically.

> Thanks Rick. I have some spreadsheets in which column width is
> automatically expanded when inputting information and some that don't. I,
[quoted text clipped - 29 lines]
> >
> > Rick

Signature

Dave Peterson

Reply to this Message