Changing column widthd

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

Thanks.

Bill Ridgeway 


0
Bill
12/14/2007 8:14:16 PM
excel 39879 articles. 2 followers. Follow

7 Replies
384 Views

Similar Articles

[PageSpeed] 38

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


"Bill Ridgeway" <info@1001solutions.co.uk> wrote in message 
news:uYBTi3oPIHA.4740@TK2MSFTNGP02.phx.gbl...
> How can you configure Excel to automatically expand column width when 
> inputting information?
>
> Thanks.
>
> Bill Ridgeway
> 

0
12/14/2007 11:09:08 PM
or just:

  target.entirecolumn.autofit



"Rick Rothstein (MVP - VB)" wrote:
> 
> 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
> 
> "Bill Ridgeway" <info@1001solutions.co.uk> wrote in message
> news:uYBTi3oPIHA.4740@TK2MSFTNGP02.phx.gbl...
> > How can you configure Excel to automatically expand column width when
> > inputting information?
> >
> > Thanks.
> >
> > Bill Ridgeway
> >

-- 

Dave Peterson
0
petersod (12005)
12/14/2007 11:16:51 PM
Doh! But of course <imagine sound of hand slapping forehead>.

Rick

"Dave Peterson" <petersod@verizonXSPAM.net> wrote in message 
news:47630EE3.58D0E634@verizonXSPAM.net...
> or just:
>
>  target.entirecolumn.autofit
>
>
>
> "Rick Rothstein (MVP - VB)" wrote:
>>
>> 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
>>
>> "Bill Ridgeway" <info@1001solutions.co.uk> wrote in message
>> news:uYBTi3oPIHA.4740@TK2MSFTNGP02.phx.gbl...
>> > How can you configure Excel to automatically expand column width when
>> > inputting information?
>> >
>> > Thanks.
>> >
>> > Bill Ridgeway
>> >
>
> -- 
>
> Dave Peterson 

0
12/15/2007 1:03:00 AM
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 


0
Bill
12/15/2007 9:35:53 AM
> 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 

0
12/15/2007 10:46:34 AM
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

"Rick Rothstein (MVP - VB)" <rickNOSPAMnews@NOSPAMcomcast.net> wrote in 
message news:O1pkJewPIHA.6036@TK2MSFTNGP03.phx.gbl...
>> 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 


0
Bill
12/15/2007 3:10:09 PM
If the columnwidth has been autofit (or not touched), then numeric data will
expand the columnwidth.

Text data won't change the columnwidth automatically.

Bill Ridgeway wrote:
> 
> 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
> 
> "Rick Rothstein (MVP - VB)" <rickNOSPAMnews@NOSPAMcomcast.net> wrote in
> message news:O1pkJewPIHA.6036@TK2MSFTNGP03.phx.gbl...
> >> 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

-- 

Dave Peterson
0
petersod (12005)
12/15/2007 3:47:24 PM
Reply:

Similar Artilces:

changing report headings names
I would like to give the user the option of changing the report heading names, I can do this with the Docmd.openreport, stdocname, acviewdesign,,,achidden and then grab the current heading and display that heading and allow the user to change it. I then do a docmd.close acreport, stdocname, acSaveYes, this works just fine when I test but when I compile the program into a mde it doesn't like the it. Is there another way of doing this? Thanks for any help. Tom you can write the current heading names to a table, making sure the table holds only one record. add the table to the quer...

Change of public IP for fighting spam
Hi, If I ask my dns registor to change my ip to another public ip. Is this going to help us fight spam? Is there's a risk of not getting email? Example old: www.company.com = 60.1.1.1 Examble new: www.company.com = 60.1.1.5 By the way, I noticed that most big companies cannot be ping anymore. Is this advisable too? Thanks, Ricky On Thu, 20 Apr 2006 08:06:02 -0700, RickyVene <RickyVene@discussions.microsoft.com> wrote: >Hi, > >If I ask my dns registor to change my ip to another public ip. Is this >going to help us fight spam? Is there's a ris...

How do I change a flyer (word document) into a picture?
I created a flyer and I want to change it into a picture so I can upload it as a picture on facebook. How do I change the 2007 word document into a picture? This newsgroup is for Microsoft Project, a planning and scheduling application. Questions about Microsoft Word, should be published to the Word newsgroup. To address your question, try using File/Save As... and select type PDF. If the document must be an image format (such as BMP, JPG, GIG, etc) you will probably need some screen capture software such as Snag-It or the clipping tool available with Windows Vista and later. ...

Shortcut Key For Column Autofit
Dear Friends , Pls Tell me what is the shortcut key for autofit coloumn in Excel 2002 . like, for format cell we can press Ctr+1. L, is there any shortcut for dooing the autofit column..Pls.. Regards, Mahesh Double-click on the right-hand border of the column header -- Kind Regards, Niek Otten Microsoft MVP - Excel "Mahesh Kumar K Hegde" <maheshinna@hotmail.com> wrote in message news:u$mCBKI9EHA.2016@TK2MSFTNGP15.phx.gbl... > Dear Friends , > > Pls Tell me what is the shortcut key for autofit coloumn in Excel 2002 . > like, for format cell we can press C...

Not allow entering repeated references in a column
Frank, Sorted! :) :) :) ... sth was missing (<) The formula to be used is =COUNTIF($A$1:$A$25,A1)<=1 and NOT =COUNTIF($A$1:$A$25,A1)=1 ... little things make the difference, isn�t it ;) ? Thank you very much for your support and your time. Ritinh -- ritinh ----------------------------------------------------------------------- ritinha's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=1566 View this thread: http://www.excelforum.com/showthread.php?threadid=27195 ...

Hyperlink Changed
The local power company had a problem last week. The power nearly went off and came back rapidly several times. I have a spreadsheet with a lot of hyperlinks to other files. After the power fluctuations the hyperlinks have the wrong path names to the files. The file names are all correct, but the path to them are all wrong. Is there a way to restore the proper path name in mass, or must I correct each and every one? The wrong path names are all the same. Does anyone know how this could have happened in the first place? ...

why cant i change font
i select all the text in a textbox and try to apply a font. nothing happens. that's the case with 4/5 fonts, only a few work. the same appears in both publisher 2003 and 2007. Are you creating a web page? Publisher will show 11 or so fonts when you are creating web pages. To use all the fonts, on the toolbar, Format, Font, uncheck Show only Web fonts. If above is not the issue, are you getting an error? -- Mary Sauer MVP http://msauer.mvps.org/ "ayudameconpublisher" <ayudameconpublisher@discussions.microsoft.com> wrote in message news:1BE1262A-A70B-4...

Change the Default Language Setting
Is it possible to set the language to 'English (Australia)' so it will be the default across all office applications? I keep having to change it manually in outlook at the moment. ...

Best way to change style of a table
I have a table I added via VBA like this: set oTable = ActivePresentation.Slides("mySlide").Shapes.AddTable(...) I then add some cells to it by: oTable.Cell(...).Shape.TextFrame.TextRange.Text = "some text" However, I cannot get a handle on these cells to update the font, fill, etc. What is the best way to do this? On Sun, 13 Dec 2009 21:01:01 -0500, Clifton Ivey <clifton ivey <"africom.mil>"> wrote: > I have a table I added via VBA like this: > > set oTable = ActivePresentation.Slides("mySlide").Shapes.AddTa...

"change order form"
Does anyone have a change order template that I can review? ...

changing from outlook 2000 to outlook xp
i just bought a new computer and want to move all my outlook 2000 personal folders from my old computer--to my new computer which is running outlook xp. What's the best way to do this? thanks, Sammy http://www.slipstick.com/config/backup.htm --� Milly Staples [MVP - Outlook] Post all replies to the group to keep the discussion intact. After searching google.groups.com and finding no answer sammy russo <sammy@aorafting.com> asked: | i just bought a new computer and want to move all my | outlook 2000 personal folders from my old computer--to my | new computer which is runnin...

Changing formula in multiple cells or range simultaneously
I am trying to change the value in multiple cells in a large worksheet simultaneuously. I want to identify the range and then adjust the formula in the entire range. Is there a way that I can highlight the range and then change to formula in each, simultaneously? For example, if I wanted to double the value in the entire range, how would I do this? Thanks, Michael You could put 2 in an empty cell. Copy it Edit|Paste special|click on Multiply under the operation section. Then clear out that 2. But it really depends on what kind of change you're making. If you wanted to ad...

Changing margins on different pages in document templates.
I was wondering how you change the margins for different pages in a template. Specifically, my firm is using a letterhead for the first page but blank pages after that so the header and footer margins on the first page ONLY must be larger than normal, while the following pages are fine with default settings for the margins. How do you do this and save it as a template? See http://sbarnhill.mvps.org/WordFAQs/Letterhead.htm, especially the "More complex letterhead" section. -- Suzanne S. Barnhill Microsoft MVP (Word) Words into Type Fairhope, Alabama USA http://wo...

Date Changes to 01/00/00 when i enter any date.
Using XL2003, I have a problem. When i enter a date in cell, such as 08/25/10, or use the shortcut Ctrl ;, XL displays 01/00/00 in the cell instead. I have formatted the cell as a date. What can I do to fix this problem? Thank you so much. Tonso ...

How to change Marketing list member "All members" to "Active membe
How to change Marketing list member "All members" to "Active members" in CRM 4.0? Any help is appreciated. I have used the following code giving object required error code: /************************************************************** * Change the default view of the Marketing List Members (Account) **************************************************************/ if (crmForm.all.createdfromcode.DataValue == 1) { if (crmForm.FormType != 1 && crmForm.FormType != 5 && crmForm.FormType != 6) { /************************************************************** ...

Sorting column data
How do I sort the data in the columns, I want to make the appear in alphabetical order? Alan I recommend Excel help. Search for: Sort a range. -- Steve "alan.holmes" <alan.holmes27@somewhere.net> wrote in message news:3S9sk.133571$Mn3.46670@newsfe30.ams2... > How do I sort the data in the columns, I want to make the appear in > alphabetical order? > > Alan > > "alan.holmes" <alan.holmes27@somewhere.net> wrote in message news:3S9sk.133571$Mn3.46670@newsfe30.ams2... > How do I sort the data in the columns, I want to make the appe...

word doc lost all changes
Version: 2008 Operating System: Mac OS X 10.6 (Snow Leopard) Processor: Intel I made lot's of changes to a word doc on Friday, after every few minutes I saved the document. Now that I have opened it again today, it seems to be in it's original format with absolutely no changes. <br><br>Is it possible I could have deleted the saved document rather than the original - but i renamed it with a different version! Can I back up the Mac to Friday evening so that both docs will be there? <br><br>Any other suggestions?? <br><br>Help - please! Hi Dawn...

How do I change reply to or senders address
How do I change the Reply to: email address or the sender’s address? My infrastructure consists of: 1 – Windows 2003 AD box 1 – Exchange 2003 box Many – Outlook 2003 email clients. Is there a way to change the senders email address, example; An email note that’s generated by joe in the domain test.foo.com (joe@test.foo.com) can this address be changed in some setting in either Exchange or Outlook, so that the recipient of the note sees the senders address as joe@foo.com? The Outlook clients are configured for an Exchange connection. On Tue, 1 Mar 2005 11:03:46 -0800, HMR <HMR@discu...

rule
There is a nice rule at outlook express so that messages from certain poeple are in different colors. Why there is not such rule at Outlook 2003? There is and it's even better! See; http://www.howto-outlook.com/howto/coloremail.htm and http://www.howto-outlook.com/howto/coloremailadvanced.htm -- Roady [MVP] www.howto-outlook.com Tips of the month: -Setting Permissions on a Mailbox -Create an Office XP CD slipstreamed with Service Pack 3 ----- "constantinos" <anonymous@discussions.microsoft.com> wrote in message news:5B1EAC31-909A-4088-97EB-BD6A97A71EC0@microsoft.com.....

Change what populates subject line when send pdf report in email
When I choose to send my POP Purchase Order Blank Form to a mail recipient (pdf), information is populating the subject line and body of my e-mail by default. Specifically the name of the report is populating the subject line. Is there a way to change this? I would rather see the PO number populating that field. ---------------- This post is a suggestion for Microsoft, and Microsoft responds to the suggestions with the most votes. To vote for this suggestion, click the "I Agree" button in the message pane. If you do not see the button, follow this link to open the suggest...

distinct values in column
I like to format a column such that it allows only distinct values. fo eg in the column A i have 10 numbers 1to 10 (A1 to A10) . if i ente any value between 1 and 10 in A11 it should not allow. A11,A12.....et should allow only distinct values. pls help me to do this thanks in advanc -- parthaemai ----------------------------------------------------------------------- parthaemail's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=3131 View this thread: http://www.excelforum.com/showthread.php?threadid=51928 Data > Validation > Allow whole number > Betw...

hiding columns #5
Hi, I'm trying to hide columns certain columns in my sheet using VB macro. Hiding of column should be based on the selection made by the user. for example I wanna hide Columns X-AW for a view and for other view wanna hide Column A -X. The logic I'm using is in column X I hav placed a pointer "eofr" I look for that using Find method. That works fine but after that th Range I'm giving to hide Columns fails :( . Please see code below. An help will be appreciated! :) Set ExcelLastCell = ActiveSheet.Cells.SpecialCells(xlLastCell) LastColumn = ExcelLastCell.Column Column =...

How to rename a column title in excel?
How to rename a column title in excel? If you mean to change the "A", "B", "C" column headers, you can't. -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting, LLC www.cpearson.com "atta" <atta@discussions.microsoft.com> wrote in message news:D0190E8D-61DA-4460-9210-1BD33CEA7E65@microsoft.com... > How to rename a column title in excel? Tools > Options > GeneralTab > check the box marked "R1C1 reference style".........that will change columns A, B, C, etc to columns 1, 2, 3, etc..... othe...

Duplicate Columns on Different Sheets
Assuming I have a column on Sheet1 named "Customers1" and another colum named "Customers2" on Sheet2, is there a way that I can automaticall copy any data inputed from Customers1 to Customers2? I currently use =IF(ISBLANK(Sheet1!A1),"",Sheet1!A1) and drag a necessary. This works well but if I insert rows into Sheet1, then must remember to insert the same row into Sheet2. I was thinking if I could automtically copy an entire column, thi would a) make things easy and b) decrease the possibility of error -- Message posted from http://www.ExcelForum.com Andrew, ...

Outlooks "Incoming mail (POP3)" keeps changing to "local"
Hi everyone, I have a question on Outlook 2000, every few days my account setting for the "Incoming mail (POP3)" change from the mail server I have specified to "local" and the mailServer then gets added to the "Account name" /meMailServerName Any ideas on why is it doing this, and can I stop it from doing this, cause each time it does, I have to manually change it back to original setting so I can get my mail. Shawn This is your antivirus software, most likely (or antispam software). Outlook doesn't do this. Check with the mfr. to see if they have an...