Wrap text and row autofit

Hello,

If Wrap text is enabled for a cell, the row autofit function works properly.
But if I merge 2 or more cells in a row, then autofit doesn't work. It sets
itself to the height of 1 row even if the contents require more than 1 row's
height.

Pls help.

Note: Excel 2003 version.
thanks,
GK


0
GK
1/4/2007 3:57:43 AM
excel 39879 articles. 2 followers. Follow

2 Replies
754 Views

Similar Articles

[PageSpeed] 25

GK

Rows with Merged cells will not Autofit.  Just one of many problems with merged
cells.

You need VBA code to do that.

Here is code from Greg Wilson.

Private Sub Worksheet_Change(ByVal Target As Range) 
Dim NewRwHt As Single 
Dim cWdth As Single, MrgeWdth As Single 
Dim c As Range, cc As Range 
Dim ma As Range 

With Target 
If .MergeCells And .WrapText Then 
Set c = Target.Cells(1, 1) 
cWdth = c.ColumnWidth 
Set ma = c.MergeArea 
For Each cc In ma.Cells 
         MrgeWdth = MrgeWdth + cc.ColumnWidth 
Next 
Application.ScreenUpdating = False 
     ma.MergeCells = False 
      c.ColumnWidth = MrgeWdth 
       c.EntireRow.AutoFit 
        NewRwHt = c.RowHeight 
       c.ColumnWidth = cWdth 
     ma.MergeCells = True 
    ma.RowHeight = NewRwHt 
   cWdth = 0: MrgeWdth = 0 
Application.ScreenUpdating = True 
End If 
End With 
End Sub

This is sheet event code.  Right-click on the sheet tab and "View Code"
Copy/paste the code into that sheet module.

Gord Dibben  MS Excel MVP


On Thu, 4 Jan 2007 09:27:43 +0530, "GK" <a.b@c.d> wrote:

>Hello,
>
>If Wrap text is enabled for a cell, the row autofit function works properly.
>But if I merge 2 or more cells in a row, then autofit doesn't work. It sets
>itself to the height of 1 row even if the contents require more than 1 row's
>height.
>
>Pls help.
>
>Note: Excel 2003 version.
>thanks,
>GK
>

0
Gord
1/4/2007 4:14:00 AM
Thanks ... it really helped.

"Gord Dibben" <gorddibbATshawDOTca> wrote in message
news:mlvop21muqtvubb438ee27i4duseqgdbi9@4ax.com...
> GK
>
> Rows with Merged cells will not Autofit.  Just one of many problems with
merged
> cells.
>
> You need VBA code to do that.
>
> Here is code from Greg Wilson.
>
> Private Sub Worksheet_Change(ByVal Target As Range)
> Dim NewRwHt As Single
> Dim cWdth As Single, MrgeWdth As Single
> Dim c As Range, cc As Range
> Dim ma As Range
>
> With Target
> If .MergeCells And .WrapText Then
> Set c = Target.Cells(1, 1)
> cWdth = c.ColumnWidth
> Set ma = c.MergeArea
> For Each cc In ma.Cells
>          MrgeWdth = MrgeWdth + cc.ColumnWidth
> Next
> Application.ScreenUpdating = False
>      ma.MergeCells = False
>       c.ColumnWidth = MrgeWdth
>        c.EntireRow.AutoFit
>         NewRwHt = c.RowHeight
>        c.ColumnWidth = cWdth
>      ma.MergeCells = True
>     ma.RowHeight = NewRwHt
>    cWdth = 0: MrgeWdth = 0
> Application.ScreenUpdating = True
> End If
> End With
> End Sub
>
> This is sheet event code.  Right-click on the sheet tab and "View Code"
> Copy/paste the code into that sheet module.
>
> Gord Dibben  MS Excel MVP
>
>
> On Thu, 4 Jan 2007 09:27:43 +0530, "GK" <a.b@c.d> wrote:
>
> >Hello,
> >
> >If Wrap text is enabled for a cell, the row autofit function works
properly.
> >But if I merge 2 or more cells in a row, then autofit doesn't work. It
sets
> >itself to the height of 1 row even if the contents require more than 1
row's
> >height.
> >
> >Pls help.
> >
> >Note: Excel 2003 version.
> >thanks,
> >GK
> >
>


0
GK
1/4/2007 4:50:45 AM
Reply:

Similar Artilces:

Formula with text
We do fixed assets on excel spreadsheet. Each worksheet is an new month. Formula is =K47*$M$2. The M represents the current month (7) and this number is entered on Row 2 M. We acquired the new asset in February and we only want to depreciate it for six months. This formula no longer works if we enter the current month of (7) in M. Can you help me? DD, What does the K47*M2 represent with respect to the fixed assets or what is K47? When you say you only wish to depreciate the new asset for 6 months, is that its entire useful life or are you taking a 1/2 year convention where 1/2 of...

How do I undue column and row underlining?
I tried to use the underline feature in a workbook;I am using several worksheets within the workbook. When I chose the underline option in a specific worksheet, excel underlined the following: 1. all the text in every worksheet in the workbook 2. the header titles in every worksheet in the workbook, and 3. the column lettering and row numbering. Why does this occur? How do I undue? First place I would look is under Format>Styles and see what style is being used. Modify if necessary. Second place to look is under Tools>Options>General "Standard Font" Gord Dibben Ex...

Text wrapping difficulity
I am attempting to wrap text around a graphic, an oval. On the "outside" of the oval is a picture that ends on the ovals left hand side. I want the text to follow the "inside" (or right hand side) of the oval. I have edited the text boxes' wrap points, that did nothing. I have grouped the three objects (picture, graphic & text box) and brought the graphic to the front. That didn't work. The text always runs through (on top of) the graphic and the picture, or goes away completely. Is there a solution? Thanks. Text will never wrap inside the oval....

remove the text preview in a slide text box
My slide has a text box and the bullits are grayed out until I select them in my presentation. How can I remove the grayed out preview? That is just a placeholder until you put text into it. It does not show up until your in the slide presentation mode. If you want a blank slide without a place holder you need to select new slide and select the blank option. -- Michael Koerner MS MVP - PowerPoint "Ken V" <Ken V@discussions.microsoft.com> wrote in message news:F33DE7C8-4082-44A4-8499-91094BBA7FCC@microsoft.com... My slide has a text box and the bullits ar...

Help on adjusting # of rows
I am creating a workbook for my estimating company. To link jobs together throughout the different spreadsheets, i have clicked on the column, copied it , and paste specialed a link to the other spreads. The only problem with this is that it has made the size of my document 28 MB... My question is, is there any way that you can manually adjust the amount of rows in a spreadsheet, or even more helpful, is there a way that you can only print cells that have actual text inside of the cell. If this is not possible, I have to manually go in and highlight what i want to print, and deal with the 28 m...

Calculating text boxes
I'm running a report on how to split up the fee for a band. Because all members of the band aren't equal (eg road crew work on a fixed fee) I have a code allocated to each member. Members Crew Agent In my query I have calculated a subtotal after the fixed fees are taken out and I now want to divide the remainder up amongst the Members equally. In my report I now have a field [FeeSubtotal] and a calculating text box called Text 27 with the control source =-Sum([Code]="Member") As an example, when I run the report the field show FeeSubtotal $5170 and the Text box showing ...

Cells with same text but excel think it's different?
I have two cells with exacely the same text. but excel think it's different and not returning any value for my vlookup.. I used if statement and the result is showing the 2 cells are different. I checked format, space and even '. can someone please help? thanks. Do you have an 0 instead of a O? (Number zero, letter O) -- Regards Andy Wiggins www.BygSoftware.com Home of "Save and BackUp", "The Excel Auditor" and "Byg Tools for VBA" "Jason" <Jason@discussions.microsoft.com> wrote in message news:84F83A62-88D7-4292-9B2E-B8F34B737B48@mic...

Wrapping Text to next cell?
Is it possible to get text that overfills a cell to wrap to the nex cell below it? thanks Bria -- Message posted from http://www.ExcelForum.com Hi no, but you can make it wrap in the SAME cell (Format - Cells - alignment' -- Regards Frank Kabel Frankfurt, Germany "bdunk >" <<bdunk.19rdkw@excelforum-nospam.com> schrieb im Newsbeitrag news:bdunk.19rdkw@excelforum-nospam.com... > Is it possible to get text that overfills a cell to wrap to the next > cell below it? > > thanks Brian > > > --- > Message posted from http://www.ExcelForum.com/ ...

Inserting a new row that has combo boxes
Hi I am creating a form that will collect telephone stats. I have it set up so that the user can select the information from a number of combo boxes. What I would like to do is create a button that will insert a new row into the bottom of this form that will have combo boxes in them. Presently, I can only copy/paste the row if the toolbox editor is open. I thank you for any help you can provide. Sean ...

Funky text justification
This is a multi-part message in MIME format. --------------040208070607020604080801 Content-Type: text/plain; charset=us-ascii; format=flowed Content-Transfer-Encoding: 7bit Publisher 2007. Every so often, when I have a paragraph set to full justification, the first few lines will be fine, and the next one will be spread out like this for no reason and then the next line will be fine. Sometimes I can fool with it and fix it, especially if the fu...

Changing Columns to Rows #2
Is there a way to change a spreadsheet so that the columns become row and the rows become columns... Other than retyping the whole thing? Thanks -- jenbrunso ----------------------------------------------------------------------- jenbrunson's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=2377 View this thread: http://www.excelforum.com/showthread.php?threadid=46750 jenbrunson Wrote: > Is there a way to change a spreadsheet so that the columns become row > and the rows become columns... Other than retyping the whole thing? > Thanks! Hi jenbrunson Se...

Wrap to Window
Version: 2008 Operating System: Mac OS X 10.5 (Leopard) In draft view, I'd like the text to wrap to the window size rather than the document's margins. Is this feature part of Word for Mac? It's easily done in the PC version. Thanks, Robin Hi Robin - Word> Preferences> View - check the box for Wrap text to window. HTH |:>) Bob Jones [MVP] Office:Mac On 4/12/08 10:20 AM, in article ee98414.-1@webcrossing.caR9absDaxw, "Robin@officeformac.com" <Robin@officeformac.com> wrote: > Version: 2008 > Operating System: Mac OS X 10.5 (Leopard) > &g...

word wrap in publisher
I am wanting to know if there is a way to reset the default word wrap values in publisher. I have everything else set so that I don't have to open the 'format text box' box every time to set this. I am writing newsletters and need to do this quickly at least 20 times an hour. HELP!!! mewithad <mewithad@discussions.microsoft.com> was very recently heard to utter: > I am wanting to know if there is a way to reset the default word wrap > values in publisher. I have everything else set so that I don't have > to open the 'format text box' box every time to...

Add Text Field Columns
PFDB: IIf(Nz([PFDirect])+Nz([PFBulk])=0,"",Nz([PFBulk])+Nz([PFDirect])) I have two fields both of which are text. When I try to add them as above, I get a concatenated result, not the sum. How does one add them? -- On Fri, 18 May 2007 17:38:49 -0600, bw wrote: > PFDB: IIf(Nz([PFDirect])+Nz([PFBulk])=0,"",Nz([PFBulk])+Nz([PFDirect])) > > I have two fields both of which are text. When I try to add them as above, I get a > concatenated result, not the sum. How does one add them? How does one add "This is text." + "this is also text&qu...

Bold some text within a text box
I have a text box hosting a memo field. I have had a user request for the ability to bold some words within the text box. I suspect it is not possible within a text box but is there a way to do it? Or will it require a custom control? If it requires a custom control, what would be involved in changing over? Presumably existing text will automatically come in unformatted but are there any other things to consider? -- Len ______________________________________________________ remove nothing for valid email address. It is possible only in Access 2007. In table ...

A macro for deleting lines of text
Hi I need to delete all lines except those containing the Text NMI, FromDate>, To Date> Can someone tell me where I'm going wrong with this Macro when I run it, it deletes all lines? Public Sub DeleteNonSpecific Rows() Dim rCell As Range Dim rDelete As Range For Each rCell In Range("A1:A" & _ Range("A" & Rows.Count).End(xlUp).Row) With rCell If Not (Text) = " FromDate>" Or _ (Text) = " ToDate>" Or _ ...

Wrap not wrapping
I go FORMAT, click CELLS, click NUMBER, click TEXT, click WRAP TEXT as I usually do but for some reason it does not wrap. Anyone have a clue? Maybe I need to turn the machine off and let it recharge... Ralphael, the OLD one Ralpael Format>Cells>Alignment tab. Wrap text is on this Tab. Checkmark it a couple of times to set and reset it. Then make sure the row is formatted to "Autofit". If you are dealing with "merged" cells, this presents a whole 'nother problem as autofit does not work with merged cells unless you use code. Gord Dibben Excel MVP On 28 ...

4-Sided Text Border
I want to print continuous text all around the outside border of my document . The only way I can figure out how to do it is use 4 text boxes & line 'em up as a border. Which I'll do if that's the only way . . . Is there a way to make one continuous text border around a document? Thanks so much! No way in Publisher to make one continuous text box. Your way (4 text boxes) will work. You could copy/paste special as a .png, this will allow cropping. -- Mary Sauer MSFT MVP http://office.microsoft.com/ http://msauer.mvps.org/ news://msnews.microsoft.com "Jan" <...

Displaying simple URL in CDialog as underlined text
Hi all, First of all I'd like to say that I'm quite new to MFC and Win32 APIs and my question might seem trivial to HC MFC programmers. I have a dialog based MFC application. When the user selects Help->About.. the application shows yet another dialog with some information on it. There's an URL too on the dialog. I've managed to make the URL, which is a CStatic text, clickable and once clicked it opens up a web browser using ShellExecute. However I'd like the text to be blue and underlined to show the user that it is a hyperlink indeed. Now it is just like ...

Access 2007-Report (Text Wrapping)
Is it possible to have text wrapping of a field on a report like the text wrapping function in a cell in a worksheet in Excel? Set the Can Grow property to Yes. -- Build a little, test a little. "SueW" wrote: > Is it possible to have text wrapping of a field on a report like the text > wrapping function in a cell in a worksheet in Excel? Thank you. "KARL DEWEY" wrote: > Set the Can Grow property to Yes. > > -- > Build a little, test a little. > > > "SueW" wrote: > > > Is it possible to hav...

Convert columns from yyyymmdd (text) to dd/mm/yyyy (date)
Hi At the moment I'm trying to move some data from our company mainframe to a SQL 2000 database. I've been given a delimited file which I put into Excel before using DTS to import into sequel. However, all dates in the CSV are stored as yyyymmdd and as a text format in excel. I've searched these forums for answers on how to convert these fields to a dd/mm/yyyy format, but I couldn't get any suggestions to work properly. (It doesn't really matter if excel changes it to a string of 'dd/mm/yyyy' as opposed to a date field long as it's in that format. I'd be r...

How to convert text in a graphic file into normal text that MSWord
I have text in a jpec or gif file. How do I convert that to normal text that MSWord can process? You'd have to use OCR software (Optical Character Recognition). Office comes with something called Microsoft Document Imaging, which does a pretty decent job, but it only reads a limited number of graphics formats (I had to convert jpg's to tif's). It may not have been installed on your computer by default -- you may have to get out your Office distribution disk. My HP all-in-one also came with OCR, but I haven't found a way to get it to read a file that it didn't j...

wrap
Hi i want set wrap on as default in excel. what must i do thank you Open a new workbook. Right-click on a sheet tab and "select all sheets". CTRL + a to select all cells. Format>Cells>Alignment>Wrap Text checked. Select A1 then right-click on a sheet tab and "Ungroup sheets". File>Save As Type: scroll down to Excel Template(*.XLT) and select. Name your workbook "BOOK"(no quotes). Excel will add the .XLT to save as BOOK.XLT. Store this workbook in the XLSTART folder usually located at........ C:\Documents and Settings\username\Application Da...

copy / paste selective rows
Hi all, I'm trying to copy a row of formulas and paste selectively (every 7th row) the same formulas (keeping them relative to their row). FYI the 6 rows in between consist of some #'s and some formulas. Thus, trying to use go to > special > formulas doesn't do the trick. Is there a macro that lets me copy and paste every x rows and loop until it reaches the end of my data? Thanks! -- Message posted via http://www.officekb.com Perhaps something along these lines .. Suppose you have in Sheet1 in C1: =SUM(A1:B1) in C8: =SUM(A8:B8) in C15: =SUM(A15:B15) and so on In S...

parsing out dates from text
I have a customer tracking program (ADP), and it was originally designed with teh customer notes as a "text" field. Then then customer notes woudl be entered at various times, it would be entered by automatically adding a lineedn cahracter, followed by the date, followed by the user name in parentheses. However, the customer notes are not very useful for searching or for filtering for various types of comments. A sample of a customer notes data woudl look like this: 10/26/05 (Stephen): Fax Submitted to Amy regarding Shipment Approval 11/2/05 (julie):approved extension 1/24/0...