MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Auto column width


Posted by Chris D on January 15, 2002 3:33 AM

Hi all,

Sometimes, when I type a number into a column that isn't wide enough, the column automatically jumps to the right width to fit the number

Not very often though; whereas most times it just displays the "########" that lets us all know the column width isn't big enough for the number.

Is there a setting somewhere that will make this width-correction automatic ?

I can't find it :-(

(Excel 97, Win 95, Novell)

Many thanks
Chris


Posted by Tom Urtis on January 15, 2002 3:51 AM

Here's one suggestion but be aware that it will auto fit all columns regardless of whether the data is text or numbers.

It's possible to evaluate each entry as being text or numbers but instead of bogging the file down with excess code, try augmenting your worksheet's fields (columns) that are meant for text, by formatting them ahead of time via Format > Cells > Alignment tab.

This may get you started; paste it into your worksheet module.

Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)
Cells.Columns.AutoFit
End Sub

HTH
Tom Urtis

Posted by Chris D on January 15, 2002 4:19 AM

Excellent ! thanks Tom (nt)