Excel help please!

  sebiven 19:08 14 Jan 2008
Locked

I have quite a involved spreadsheet that I've just set up but have one issue regarding entering text in various columns.

The text I enter is of varying length and sometimes I have to increase the column width to accomodate it.Often text entries are deleted and/or replaced,more often than not using copy/paste.

So to my question - can I make the column width automatic to suit the length of text?

Thanks!

  hastelloy 19:18 14 Jan 2008

Format, Column, Autofit Selection. But only after text has been added. You can get the text to wrap to another line(s) within the cell by using Format, Cells, Alignment and then tick Wrap Text (about half-way down). Someone may give you another way but this is the best I can do.

  Fermat's Theorem 19:55 14 Jan 2008

to wrap text in a cell is, I think, to press Alt and Enter simultaneously at the point(s) where you would like the text to wrap.

:-)

  VoG II 21:40 14 Jan 2008

Assuming that you want the column width to resize rather than text wrap then:

Right click the sheet tab and select View Code. Copy and paste in

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Count > 1 Then Exit Sub
Target.Columns.AutoFit
End Sub

Close the code window.

  Simsy 21:40 14 Jan 2008

achieve what you want with a macro, but a quick way to do it manually is to double click on the line at the right hand edge of the column letter...

eg if the column in question is A, double click on the line between the A and B columns.

In the meantime I'll see if I can beat Vog writing a macro.

(I doubt it!)

Regards,

Simsy

  VoG II 21:44 14 Jan 2008

Sorry Simsy :o)

  Simsy 21:52 14 Jan 2008

but without extensive testing, and if there are other macros using the "Selection change" event there may be other consequences...

For the purposes of this I'm assuming the colum in question to be column A. Change the following text as appropriate if it's another column...

Right click on the tab for the sheet in questin and select "View code"

In the box that appears, in the right hand panel and paste in what appears below, between the rows of Xs the following

xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

If Not Application.Intersect(Target, Range("A:A")) Is Nothing Then
Columns("A:A").EntireColumn.AutoFit
End If

End Sub

xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx

You'll have to save the sheet and choose to allow macros.

I think that will work!

Good luck,

Regards,

Simsy

  Simsy 21:54 14 Jan 2008

But at least I think mine works!

Regards,

Simsy

  VoG II 22:11 14 Jan 2008

Are you suggesting that mine doesn't?

  sebiven 04:12 15 Jan 2008

Now now children :-))

VoG™ and Simsy - many thanks!

  Simsy 16:18 15 Jan 2008

No No No... of course not!
My bah humbug was that you'd beaten me too it... my subsequent, "at least" was, my compensation for that.

I think we all know that your solutions work!

Regards,

Simsy

This thread is now locked and can not be replied to.

Elsewhere on IDG sites

OnePlus 5 review

Alice Saey's mesmerising animation for Dutch singer Mark Lotterman

iPad Pro 10.5in (2017) review

Comment booster votre iPhone ?