insert row when cell full, auto wrap

Hi there,
I used this code in ver 2003 to insert a row and wrap text in a cell..BUT, 
it does not work in ver 2007. Any ideas? Much appreciated.

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

0
Utf
1/5/2010 11:16:02 PM
excel.programming 6508 articles. 2 followers. Follow

1 Replies
1060 Views

Similar Articles

[PageSpeed] 13

That code does not insert a row nor does it set a cell to wrap text.

All it does is allow autofitting rows with merged cells.

Cells must be preset to "Wrap Text"

Rows must to preset to "Autofit"

Then and only then will the merged cells autofit.


Gord Dibben  MS Excel MVP

On Tue, 5 Jan 2010 15:16:02 -0800, Tim <Tim@discussions.microsoft.com>
wrote:

>Hi there,
>I used this code in ver 2003 to insert a row and wrap text in a cell..BUT, 
>it does not work in ver 2007. Any ideas? Much appreciated.
>
>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

0
Gord
1/5/2010 11:28:33 PM
Reply:

Similar Artilces:

How do I change the color for cell selection in excel?
I just started using Excel 2007. In the older versions when I select a row, column, or specific group of cells, Excel would highlight the group in a certain color. In Excel 2007, it simply surrounds the selected group of cells with a thick black line. I've tried going into Excel Options under Advanced but couldn't find how to do this. Can you assist me in changing this feature? On Dec 31 2007, 6:52 pm, dminliberty <dminlibe...@discussions.microsoft.com> wrote: > I just started using Excel 2007. In the older versions when I select a row, > column, or specific grou...

Row numbers #2
I want to label and print row numbers for each row, however, I do not wish to print column headings...can I disable the printing of the column headings? Is there possibly a function to run in a cell to provide it a number or something else? ----------------- Scott You can only disable printing column *and* row headings. <File> <PageSetup> <Sheet> tab, Make sure "column & row headings" is *unchecked*. Then, you can number your first column to mirror the row numbers, and that will give the impression of displaying and printing the row headers. You could e...

change cell shading when a number differs from the previous number
I am generally familiar with Excel, but need some help on this particular problem. Is there a way to apply a format where: whenever the number in a column differs from the one previous to it, a cell shading color change is applied. For example, you have a column of numbers in Excel: 2 2 2 5 (cell shading color change applied) 5 5 5 2 (cell shading color change applied) 2 2 3 (cell shading color change applied) etc.... It doesn't matter what color is used, just so that it is applied whenever there is a change from the previous number. There has got to be an easy answer to this.... ...

Insert object error
In Word 2006 when I try to insert an object from a file (another Word file, Excel file, etc.) as an icon in a Word 2007 document I receive an error "Word cnnot start the converter mswrd632.wpc." What can I do to correct this problem. Thanks. JoeP Click on the Fixit button at http://support.microsoft.com/kb/973904 -- Hope this helps. Please reply to the newsgroup unless you wish to avail yourself of my services on a paid consulting basis. Doug Robbins - Word MVP, originally posted via msnews.microsoft.com "JoeP" <JoeP@discussions.microsoft.com&g...

Auto subject attached
Dear all, In my email, i have insert several people's email. When the reader click the link, the outlook will automatic launch a new email. But, how can we help them to type the subject of the new email? Regards Yian Yian <dragonyian@hotmail.com> wrote: > In my email, i have insert several people's email. When > the reader click the link, the outlook will automatic > launch a new email. But, how can we help them to type the > subject of the new email? Are you talking about a mailto: URL? If so, include the ?subject clause: mailto:user@site?subject=Your%20Subjec...

How can I stop format change when copying data into unlocked cells
How can I stop the format changing when I copy data into an unlocked cell in a format protected worksheet? I need to allow people to both enter data or copy data from another source into cells so I have unlocked these cells but also don't want the format of the cells to be changed. If you enter data directly the format does not change but if you copy and paste data it changes the format. I know you can use "copy paste special values" but would like to know if there is some system way of doing this. Copy/paste this into the sheet module. Right-click on sheet ...

Excel : insert new data series via mouse click on graphic.
Dear Developers, I work as researcher in a sector that uses spatial data. i always return back to excel, honouring Turing principle, after "travelling" to more sophisticated Sw or ambients (like matlab, and others). One of the few thing I find annoyng in excel is that one cannot input data via graphic with a click of mouse on the cartesian space, function very useful in spatial data. Sincerely ---------------- 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 ...

Putting a "period" at the end of every cell's-worth of text
Hi. I have a large number of cells in a column, each of which contains text. Some cells of text end in a period, some do not. Is it possible to write a function that would add a period to the end of each textbox? I am not worried about corner cases, i.e., where the text ends with "M.D." or suchlike. Thanks for any help - Insert an adjacent column. The following example assumes that the text is in cell A2 and the formula in B2 (The inserted column). =IF(RIGHT(A2,1)=".",A2,A2&".") Copy the formula down for the full length of the data then select the co...

INSERTING ROWS OF TEXT
HOW DO YOU INSERT ROWS OF TEXT BETWEEN ROWS WHEN YOU NEED T O ADD LINES FOR EXAMPLE IF I NEEDED TO 2 MORE ROWS BETWEEN 6 AND 7 Joe, Please don't post in all caps -- it is considered rude and is hard to read. You can add add two rows between rows 6 and 7 by selecting selecting rows 7 and 8 and choosing Row from the Insert menu. -- Cordially, Chip Pearson Microsoft MVP - Excel www.cpearson.com chip@cpearson.com "JOE" <PLAN@SBSGLOBAL.NET> wrote in message news:03a201c38e8d$dff368b0$a101280a@phx.gbl... > HOW DO YOU INSERT ROWS OF TEXT BETWEEN ROWS WHEN YOU NEED &g...

Too many different cell formats #2
I am working with a large spreadsheet (with many worksheets) that will not allow me to change/add any more cell formats and limits my additional data entry. The message pops up "Too many different cell formats". Per Microsoft's support page 213904, they instruct you to reduce the number of formats to standard formats, etc. I've tried to do that but any change backwards to standard formatting is evidently the same as new change. I'm stuck. There must be a trick to quickly change each worksheet back to a standard format. Any help would be appreciated. Excel 2003o...

Double Space Cells
How do you change a cell into a double space cell- one word on top of another in a single cell? Hi joe type the first word, press ALT+ENTER then type the second alternatively, type both words, right mouse click, choose Format / Cells, go to the alignment tab & choose wrap text - this will work if the column is only wide enough to accommodate one of the words on each line Cheers JulieD "Joe" <anonymous@discussions.microsoft.com> wrote in message news:19c1501c44d76$b9b96070$a401280a@phx.gbl... > How do you change a cell into a double space cell- one > word on top of...

how do I make a cell in Excel required to be populated?
I am attempting to use Excel for a form I am creating and need to make certain cells required. I am unfamiliar with Macros, but am thinking this may be the only way to do this. Thoughts? You could use a macro that looks at those cells and counts how many are filled in. But since macros are new, how about an alternative? Use an adjacent cell. Put a formula like: =if(c3<>"","","<----Please fill in this cell") (I used D3 for this) Then format it in a nice bright bold red. The user sees the warning immediately. If you have formulas that depend on al...

Non-VBA to gow grow based on another cell?
Hello, If I type in dog;cat;fish;cow;rat in cell A1, what I want in cell B1 is: dog cat fish cow rat stacked on top of each other. I used =substitute(a1,";",char(10)) and checking "word wrap", so now it looks like it is supposed to. But when I add more entries in cell A1, I don't know of a way to get the cell to expand row height so the entries don't scroll off. And when I reduce entries down to three, the cell should shrink. Basically, the height of the row should match the contents. Can this be done non-VBA? Thanks for all your help with this and past ques...

Prefixing a number to the number in a cell
I have in column A, 1000 numbers, now i need to prefix an identical number to all the 1000 numbers in A column. What is the easiest way?. I want the number to be prefixed in the same A column.. Pls help Regards Sherees -- Life isa journey not a destination Hi Sherees, Try this, as I don't believe you can do this with a formula. Sub PrefixItsSelf() Dim cell As Range For Each cell In Selection cell.Value = cell.Value & cell.Value Next End Sub Copy into the sheet module and select the cells you want to prefix and then run the macro. HTH Regards, Howar...

Full Text Indexing Error on CRM Install
Hi I'm getting the following error during install at select SQL Server "Full-Text Indexing needs to be running on the specified SQL server." I've installed Index Service, and also went back and reinstalled the full text module in SQL Server 2000. Still getting the error. How do I fix this? Hi Have you checked the indexing service is started in services? Regards Dan "amwbgi" wrote: > Hi > > I'm getting the following error during install at select SQL Server > > "Full-Text Indexing needs to be running on the specified SQL server....

Auto Fill Weekdays
On Excel 2008, when I use Auto Fill, and select Fill Weekdays, I get Sunday through Thursday, instead of Monday through Friday. Here's what I'm doing: * In A1, type Monday. * Grab the handle on the bottom-right of A1, and drag down. * Excel Auto Fills with Sunday through Saturday (all 7 days). * I click on Auto Fill Options, and select "Fill Weekdays." * Excel Auto Fills with Sunday through Thursday (not Monday through Friday) Any ideas how to fix this? Thanks, ~ Horatio On 3/23/10 12:00 PM, Horatio J. Bilge wrote: > On Excel 2008, when I use Auto Fill, a...

Cell colours not visible in linked document
I'm using Word 2003 in XP and have a Word document for a price list in which the data is in a linked Excel 2003 spreadsheet. For clarity, alternate rows in the Excel table have a fill colour (25% gray). The problem is that this colouring is not visible in the Word document, print preview or when output to PDF. Printing to a laser printer is fine (using PCL5e and PS drivers), but not having it visible in the PDF is a problem. Any ideas? JF. ...

cell format #5
I have a vlookup formula. =IF(ISERROR(VLOOKUP($A7,avail,7,FALSE)),"",VLOOKUP($A7,avail,7,FALSE)) the formula is looking for a date. the formula works fine. but what is happening is that if it finds the item and then column 7 is blank. the formula returns the following. 01/00/00 how can i get it so it doesnot return anything at all if there is nothing in the column. What do you want to see? I'm guessing nothing: =if(iserror(yourformula),"",if(yourformula="","",yourformula)) alternatively since you want to see "" in either cas...

Format Excel cells for "all caps", with Office 07?
How do I format Excel cells for "all caps", with Office 07? That is to say that I use an Excel form frequently, which has several cells that require a single alpha character fill in. When the form prints, the lower case characters appear small and less predominant then the body text and numbers. Thanks for your suggestions. My best suggestion is to simply use the Caps Lock key. That's what it's for. If that doesn't work, you can use the Upper function, either in the worksheet or in VBA, to change data to upper case. Regards Fred "Louisana Jim&q...

Find a value inside a range of cells
Key words: Key word Category Code ACCIDENT 1 ACCOUNT 2 ACCREDITATION 3 ADMISSION 4 AGREEMENT 5 ALARM 6 ANNUAL 7 APPLICATION 8 APPRAISAL 9 ARREST 10 -------------------------------- NMAC DocumentType Category 1.15.3.101 NON-RECORD MATERIALS 1.15.3.102 SUBJECT FILES 1.15.3.103 PRIMARY MISSION RECORDS 1.15.3.104 AGENCY ORGANIZATIONAL CHARTS 1.15.3.105 ADMINISTRATIVE RULES 1.15.3.106 ADMINISTRATIVE FILES (EXECUTIVE LEVELS) 1.15.3.107 ADMINISTRATIVE REFERENCE FILES (NON-EXECUTIVE LEVELS) 1.15.3.108 ADMINISTRATIVE CORRESPONDENCE FILES (EXECUTIVE LEVELS) 1.15.3.109 GENERAL...

How to make auto connection once OE startup?
Does anyone have any suggestions on how to make auto connection to internet once OE startup? so I don't need to manually make a connection to internet before checking emails. I am using ADSL modem under XP with SP3 and IE8. Does anyone have any suggestions? Thanks in advance for any suggestions Eric First reply to a few of your other new threads before starting another one. Eric wrote: > Does anyone have any suggestions on how to make auto connection to > internet > once OE startup? so I don't need to manually make a connection to internet > before checking...

Cell Total shown Sheet to Sheet
Question: I have an expenses/income report that is setup as a monthly sheet... Can the final Monthly total (say it is in cell AF70) be reflected from Sheet one into Sheet 2 (into cell AF 68)?? Basically, the AF70 cell in every sheet should be refelcted into the next sheets cell AF68... Rinse and repeat for every sheet inthe excel file (12 sheets in all). Thanks for any comments or help... macgilgamesh -- macgilgamesh ------------------------------------------------------------------------ macgilgamesh's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=27930 ...

More no of rows/columns in office web components?
Hi, I have read post(s) on excel having column Limit of 256 and row as 65536. But following link -> http://www.bygsoftware.com/Excel/functions/minmax.htm If u insert a new spreadsheet by using command and options button on any embedded excel workbook and then workbook tab, then it shows that there are 262144 rows and 1756 Columns.How has that been achieved.? Regards, Hari India Correction :- Please read that as 17576 columns (=26^3) Regards, Hari India "Hari" <excel_hari@hotmail.com> wrote in message news:u4CDHbKgEHA.2536@TK2MSFTNGP09.phx.gbl... > Hi, > > I...

Is it possible to plot discontiguous cells?
Hi, I posted this yesterday in Miscellaneous, but thought it would be a good idea to ask here, too. I have a column which returns data from other columns based on a set of formulas. The result in each cell can be either a number (to plot in a line chart) or a blank and/or "-" sign like so: 5 - - 3 - - - 8 I would like to be able to chart only the numbers which result in this column. The dashes and numbers will not always position in the same manner as the calculations are altered by user input (therefore I cannot merely return the next number a fixed rows down into another colum...

How can I see all my wrapped text in a cell.
I have formatted the text to wrap in the cell and how adjusted the row height to auto-fit and even larger. I can see all of the text in the formula bar (counted 1336 words) but it does not all display in the cell. I have changed the size of the font and changed the vertical alignment. This leaves white space in the cell, but still does not display all of the information. Hi enter some manual linebreaks with aLT+ENTER every 1000 characters -- Regards Frank Kabel Frankfurt, Germany "Charlene Hoffmeister" <Charlene Hoffmeister@discussions.microsoft.com> schrieb im Newsbe...