Column automatically adjusts width to fit text entries

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

redsanders - 18 Jul 2007 14:58 GMT

Not able to get an answer to this in General Questions Forum - so trying here:
Is it possible to have Excel automatically extend the width of a column as
text is entered. That is, instead of the text spilling over into the
adjoining cell, the column automatically increases in width as characters
are added. Answer needed
for one of our teachers ASAP - college instructor claims it can be done - I
am unable to find it anywhere.

Reply to this Message

Jim Cone - 18 Jul 2007 17:48 GMT

It cannot be done.

Signature

Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware
(Excel Add-ins / Excel Programming)

"redsanders" <redsanders@discussions.microsoft.com>
wrote in message
Not able to get an answer to this in General Questions Forum - so trying here:
Is it possible to have Excel automatically extend the width of a column as
text is entered. That is, instead of the text spilling over into the
adjoining cell, the column automatically increases in width as characters
are added. Answer needed
for one of our teachers ASAP - college instructor claims it can be done - I
am unable to find it anywhere.

Reply to this Message

Mike H - 18 Jul 2007 20:10 GMT

Your college instructor is both right and wrong, it's not automatic but try
this,

Right click the sheet tab, view code and paste this in.

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("A1:IV65536")) Is Nothing Then
Target.EntireColumn.AutoFit
End If
End Sub

If you want it to work on other sheets then you'll have to paste it in every
sheet.

Mike

> Not able to get an answer to this in General Questions Forum - so trying here:
> Is it possible to have Excel automatically extend the width of a column as
[quoted text clipped - 3 lines]
> for one of our teachers ASAP - college instructor claims it can be done - I
> am unable to find it anywhere.

Reply to this Message

Mike H - 18 Jul 2007 20:18 GMT

Ignore the last one, this is better.

Public OldRng As Range

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Not OldRng Is Nothing Then
OldRng.EntireColumn.AutoFit
End If
Set OldRng = Target
End Sub

Reply to this Message

redsanders - 18 Jul 2007 20:56 GMT

Thanks Mike! It works! Just haven't had the time (and/or talent) to learn
VB Script. Calling my teacher to share this with him.

> Ignore the last one, this is better.
>
[quoted text clipped - 14 lines]
> > for one of our teachers ASAP - college instructor claims it can be done - I
> > am unable to find it anywhere.

Reply to this Message

Mike H - 18 Jul 2007 21:14 GMT

Tell him you wrote it, get some Kudos!! no not really it's best to study it
and understand how it works.

Thanks for the feedback.

Mike

> Thanks Mike! It works! Just haven't had the time (and/or talent) to learn
> VB Script. Calling my teacher to share this with him.
[quoted text clipped - 17 lines]
> > > for one of our teachers ASAP - college instructor claims it can be done - I
> > > am unable to find it anywhere.

Reply to this Message

Gord Dibben - 18 Jul 2007 22:59 GMT

Mike's code is not VB Script.

It is VBA............Visual Basic for Applications and works with Office
Applications.

VB Script is a whole 'nother thing. See Windows Help for "vbscript"

Gord Dibben MS Excel MVP

>Thanks Mike! It works! Just haven't had the time (and/or talent) to learn
>VB Script. Calling my teacher to share this with him.
[quoted text clipped - 17 lines]
>> > for one of our teachers ASAP - college instructor claims it can be done - I
>> > am unable to find it anywhere.

Reply to this Message

Ross - 19 Jul 2007 03:20 GMT

Hi Mike,

First...this is great!

A couple of questions for you:

1. I notice that the 'Undo' button is only available while typing in a cell.
It's grayed-out as soon as you leave the cell. I have in the past
accidently changed a figure in a cell and then realized I shouldn't have. If
I don't remember the old figure correctly, I might not be able to fix it.

2. Also, I'm having trouble getting cell A1 to auto-adjust. I even tried
using the select all button and then pasting the code in, but it didn't make
any difference. All other cells are behaving correctly. I'm using 2002 XP,
if that means anything.

Any ideas as to how to tweak your code to for these 2 things?

Thanks..

Signature

smither fan

> Ignore the last one, this is better.
>
[quoted text clipped - 14 lines]
> > for one of our teachers ASAP - college instructor claims it can be done - I
> > am unable to find it anywhere.

Reply to this Message