Convert data into one WrapText cell

User's data is as follows:

G         Data.........................        4    no    5        20
            more Data ...............
            etc

where G through 20 is in a single row, and therefore
Data occupies 3 rows.

I believe this gives me a complete record where the Data
occupies more than one row:

Sub TestConvertToWrapText()
Dim Cell As Range, StartRw As Long
Dim EndRw As Long

For Each Cell In ActiveSheet.Range("B2:B33")
    If Not IsEmpty(Cell) Then
        StartRw = Cell.Row
        If Not IsEmpty(Cell.Offset(1, 0)) Then
            EndRw = Cell.End(xlDown).Row
        End If
    End If
Next
End Sub

Can anyone please show me how to take this record
and paste it such that it occupies a single row?

Regards.



---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.518 / Virus Database: 316 - Release Date: 11/09/2003


0
sg_booth (15)
9/17/2003 7:32:18 PM
excel.misc 78881 articles. 5 followers. Follow

1 Replies
773 Views

Similar Articles

[PageSpeed] 59

Many thanks. Helped a lot.

Regards.

"Dave Peterson" <ec35720@msn.com> wrote in message
news:3F6902D7.CA1A065D@msn.com...
> When I'm doing this kind of thing, I usually delete the row that I merged
into
> the one above.
>
> Option Explicit
> Sub TestConvertToWrapText()
>
> Dim iRow As Long
> Dim FirstRow As Long
> Dim LastRow As Long
> Dim delRng As Range
>
>     With ActiveSheet
>         FirstRow = 2
>         LastRow = 33 '.Cells(.Rows.Count, "B").End(xlUp).Row
>
>         For iRow = LastRow To FirstRow + 1 Step -1
>             If IsEmpty(.Cells(iRow, "B")) Then
>                 'do nothing
>             Else
>                 If IsEmpty(.Cells(iRow - 1, "B")) Then
>                     'do nothing
>                 Else
>                     .Cells(iRow - 1, "B").Value = .Cells(iRow - 1,
"B").Value _
>                                                & vbLf & .Cells(iRow,
"B").Value
>                     If delRng Is Nothing Then
>                         Set delRng = .Cells(iRow, "B")
>                     Else
>                         Set delRng = Union(delRng, .Cells(iRow, "B"))
>                     End If
>                 End If
>             End If
>         Next iRow
>
>         If delRng Is Nothing Then
>             'do nothing
>         Else
>             delRng.EntireRow.Delete
>         End If
>     End With
>
> End Sub
>
> And I concatenated the cells with alt-enters (vblf).  You may not want
this.
>
> Stuart wrote:
> >
> > User's data is as follows:
> >
> > G         Data.........................        4    no    5        20
> >             more Data ...............
> >             etc
> >
> > where G through 20 is in a single row, and therefore
> > Data occupies 3 rows.
> >
> > I believe this gives me a complete record where the Data
> > occupies more than one row:
> >
> > Sub TestConvertToWrapText()
> > Dim Cell As Range, StartRw As Long
> > Dim EndRw As Long
> >
> > For Each Cell In ActiveSheet.Range("B2:B33")
> >     If Not IsEmpty(Cell) Then
> >         StartRw = Cell.Row
> >         If Not IsEmpty(Cell.Offset(1, 0)) Then
> >             EndRw = Cell.End(xlDown).Row
> >         End If
> >     End If
> > Next
> > End Sub
> >
> > Can anyone please show me how to take this record
> > and paste it such that it occupies a single row?
> >
> > Regards.
> >
> > ---
> > Outgoing mail is certified Virus Free.
> > Checked by AVG anti-virus system (http://www.grisoft.com).
> > Version: 6.0.518 / Virus Database: 316 - Release Date: 11/09/2003
>
> --
>
> Dave Peterson
> ec35720@msn.com


---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.518 / Virus Database: 316 - Release Date: 11/09/2003


0
sg_booth (15)
9/18/2003 6:20:10 PM
Reply:

Similar Artilces:

Access 2.0 Converter for Access 2003
I don't have the patience for the "free" Microsoft online help. I am trying to import some old access DB files into my access 2003 software for data extraction. Thinking I would be finished in no time... BAM Access 2.0 converter needed to open file... ok. I try Microsoft online... sign in. sign in. fill form. copy Key from access. "Not a valid key" I've not yet installed my complimentary "Full Version" of office 2003. So just to be granted the privalege to download this (simple?) patch I must quit what I'm working on, Install Office...

LINEST
I would like to use the LINEST function on data that is not located in one single row or column. The data that have to be correlated are scattered along the worksheet and I would like to select them manually. How can I do this. If I try =LINEST((E72;F72;G72);(E75;F75;G75),1,1) or =LINEST({E72;F72;G72}; {E75;F75;G75},1,1) an error occurs. How can I input an array of nonadjacent cells in this function. thx Why not use some 'helper cells' with formulas like =E72 to get the numbers into a contiguous range? best wishes -- Bernard V Liengme Microsoft Excel MVP www.stfx.ca/people/bliengme ...

Reference Global vars in cell formulas?
Is it possible to reference global/public vars in cell forumulas? I want to create a function that sets a global variable say true and false, and have a cell formula that has an if statement: =if([Globalvar], do something, "") I can't use a UDF because it causes other Macros I have to fail with RunTimeError 1004. It's something about UDFs not allowed to alter other cells, but the UDF wouldn't be modiying cells, but my non-UDF macros are. So, I'm experimenting with a different approach. Ideas? Hi, If you decalre the variable like this then i...

Converting from web form to CRM lead?
Hi, is there a vanilla way to have a web form that populates CRM lead data, or is this based on each implementation's API skills? I just tried out SF.com and they make it incredibly easy to add the lead fields to any web page, and have it go straight into your CRM. I'd like to find out if MS CRM does the same, and hopefully I'm just missing it. Thanks! Chris This feature is very easy to implement using the CRM SDK if you have coding skills. You might find products from ISV's like www.c360.com would help as well. If your not up for coding your own solution then check ou...

counting blocks of data
this is a sample of a large block of data I have. A block of data is represented by the letter A until the next cell is Blank. For E.G. row 1 has 2 blocks of data. the 1st block is row1, col3 and 4 and the next block is column 9. row 2 has 2 blocks. Col3,col4 and col5 is the 1st block and column 8 & 9 is the 2nd block. Row 3 has 1 block only. column 6&7 Row 4 has 0 blocks and row 5 has 1 block because all the A's are in adjacent cells. 1 2 3 4 5 6 7 8 9 Row 1 A A A Row 2 A A A A A Row 3 A A Row 4 Row 5 A A A A Thank you all for you...

Importing Fixed Width data
Hi, I've been extracting information from my company mainframe, and placing fields into a "fixed length record" file, which I then FTP, and import into Excel 2000. Each record contains around 200 fields, and the import process is VERY painful, for two reasons. - many of the fields are single byte, and clicking to set the start/end point of each is a nightmare. - I generally need to put many of my fields into 'Text' format, as much of the data has leading zeros, which need to be retained. Is there a way that I can set up an import 'template', so that I can just...

how to make a picture pop-up when selecting a cell?
hello, is there a way to configure a cell such that when clicked, small window will pop-up, displaying a picture of your choice -- tendercar ----------------------------------------------------------------------- tendercare's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=2578 View this thread: http://www.excelforum.com/showthread.php?threadid=40205 add a picture to a comment http://www.contextures.com/xlcomments02.html#Picture --- HTH, David McRitchie, Microsoft MVP - Excel My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm "tendercare&q...

Data validation #20
Hi...I am trying to store a list in a seperate worksheet and the reference it on my other worksheets. When I go to data validation an try to do this, i get the message "you may not reference anothe worksheet or workbook for data validation." My cpu at home has exce 2003, this one has 2002. Any ideas or workarounds? Thank -- Message posted from http://www.ExcelForum.com Hi Try the following: - select your list - create a defined name for this list ('Insert - Name Define') - use this name in your data validation formula. e.g. =Name_list have a look at the following htt...

MIN ingnoring zero sum in an range of cells
I have a spread sheet where I need to use =min(a1:a10,b1:b10) but ignoring any cells with a zero in the range. Please hel. I have been searching the groups for hours. Tks in advance On Dec 22, 3:18=A0pm, Gulicio <michael.gul...@snet.net> wrote: > I have a spread sheet where I need to use =3Dmin(a1:a10,b1:b10) but > ignoring any cells with a zero in the range. Please hel. I have been > searching the groups for hours. Tks in advance Finally found answer that worked for thoose still looking here it is: try the following array formula (entered with CTRL+SHIFT+ENTER): ...

How to find the data source
Hi, and Thank you for any help. I have inherited a hand full of Pivot table spreadsheets that Pull data in from an Access DB, and the query used in the spread sheet needs to be modified (it is using some Dates that should not be hard coded). There are a few that I just can not find any Links, or Data Queries, nothing that looks like it is calling out to the data source, except it does prompt me to refresh the data when I open the file. I've found all the hidden sheets, and named ranges (I think) I've looked through all the VBA also. How do I find what I can't see?? Thanks....

How to fill cells A1, A2, A3... with contents from B1, B4, B7...
Hi, I am trying to copy contents from, say, B1, B4, B7, .... into A1, A2, A3,.... And did not know how to set up a formula to do that. I'd appreicate for any suggestions. Thanks, Xianbo If you're trying to copy every third row from B to A, enter the formula =OFFSET($B$1,ROW()*3-3,0) in A1, and autofill down through the appropriate cells in column A. "Xianbo" wrote: > Hi, > > I am trying to copy contents from, say, B1, B4, B7, .... into A1, A2, > A3,.... And did not know how to set up a formula to do that. I'd appreicate > for any suggestions. &g...

Displaying or showing a defined cell name in another cell
Version: 2008 Operating System: Mac OS X 10.6 (Snow Leopard) Processor: Intel Is there a way to display a defined cell name in another cell? <br><br>I would like to define a name for A1 to be &quot;_1A1&quot; and have that displayed in a different cell rather than the value of cell A1. I am ranking data based on the value in the cell, but would like to have the defined name returned in an adjacent cell: <br><br> A B C D E <br> 1 rank value cell name <br> 2 10 1 10 _1A1 <br> 3 2 ...

Comments in a cell
Office 2003 I am trying to add some additional information to a cell. More specifically I have a column for "Project Names" when I click on the project name I want it to give me some additional information. I know how to insert a comment and that would work for the most part but what I would like to do if possible is to create a standard addition to the cell that a user can fill out so that each cell is consistent. Is there a way to add an information box to a cell? HT I would think the safest way would be to just use an additional cell on the same row. You'll be ...

Monitor Amount Of Data Downloaded
I was wondering if there was any way of finding out how much data (emails) in terms of MB exchange processes/downloads on a monthly basis. On Tue, 6 Feb 2007 21:00:00 -0800, Stephen <Stephen@discussions.microsoft.com> wrote: >I was wondering if there was any way of finding out how much data (emails) in >terms of MB exchange processes/downloads on a monthly basis. Lots, there are 3rd party applications that take your message tracking logs and present them in graph and report formats Quest and Promodag are good packages for the large and small/med orgs respectively. Are there a...

Chart with data from multiple worksheets
Is there a way to create a chart that uses data (range) from multiple worksheets in both x and y axis. In my case I have several worksheets for different cities in one file. Within each worksheet are three columns. Column 1 identifies a point of interest in the city, column 2 measures the distance from the airport to said point of interest and column 3 has the elevation of the point of interest. I wish to chart the distances on the x axis and the elevations on the y axis using the data from the each city worksheet directly without creating another worksheet that references all the da...

reformat text in a cell
I have 2000+ cells that look like this: UK00001 Geddy Lee 2112 Bytor Ln GLENVIEW, IL 60025-1522 Is there a simple way to add a space after each line? right now when i paste it into a word doc, it shows like this : UK00001 Geddy Lee2112 Bytor LnGLENVIEW, IL 60025-1522 I would like to REFORMAT every cell to look like this with spaces in between to make it readable: UK00001 Geddy Lee 2112 Bytor Ln GLENVIEW, IL 60025-1522 any suggestions? ...

Converting VHDs from dynamic to fixed yields host DPM VSS error
Hi, I've been using DPM 2007 to back up Virtual Server 2005 VMs at the host level successfully until today. Disk space on one host was getting low and I decided to convert the VHDs comprising one VM from dynamically expanding to smaller fixed VHDs. That is easier said than done, as it turned out, but the end result was two new, fixed size VHDs containing all the same info. If I can get that to work, others may be interested in the procedure too. The resulting VM boots and runs fine, but now when I go to run a DPM online backup I get an error. The DPM console directe...

Splitting cels contents into two cells
Hi all I have this in Cell m49 Birmingham v West Ham, 15:00 The contents are always displayed with the v in the middle after the hast name a comma In this case I would like the Birmingham to appear in cell o49 and west ham to appear in p49 and ignore the 15:00 the two names change in size bur the v and comma always appear as shown above Thanks for looking Stew In O49: =LEFT(M49,FIND(" ",M49)-1) in P49: =MID(M49,FIND(" v ",M49)+3,FIND(",",M49)-FIND(" v ",M49)-3) -- Gary''s Student - gsnu200909 Thank you, and thank G...

countif, but only using partial cell contents in the reference array
=MID(a1000,FIND("{",a1000,1)+1,8) This formula returns the portion of the cell that is important to me However, if I want to match a portion of the reference cell array t the portion of the criteria cell, how do i do that? =countif(a1:a100, MID(a1000,FIND("{",a1000,1)+1,8), 0) I don't understand how to change the a1:a100 to to only lookat th MID(a1,FIND("{",a1,1)+1,8) portion of it..... much thanks, jare -- jared ----------------------------------------------------------------------- jaredh's Profile: http://www.excelforum.com/member.php?action=ge...

Removing Legacy Data from a PivotChart report
I have a pivot chart report which pulls its source data from SQL Server database. I have created a copy of this file to point to a different SQL Server (exact same tables). When I refresh the report, the chart and pivot are refreshed correctly from the new server. However the original data still remains (as well as the new data) in the drop down selection box for the columns. i.e. the chart is showing ExecuteDate in the ROW area, JobName in the COLUMN area and the number of minutes each job ran each day as the DATA. The graph plots each job showing the duration over the last 30 days. In the...

Higher precision: can one get it?
Hi All, I demonstrating the approximation of Pi by successively computing the perimeters of regular inscribed polygons with 2^n sides. Excel's precision was overwhelmed at about 4000 sides. Is there any way to get "double precision"? If worse comes to worse, I'll use something like BigDecimal in Ruby or Perl to get greater precision. -- Regards, Richard Hi Richard, Excel's precision is 15 significant digits. If you need more, you might take a look at the XNUMBERS add-in: http://digilander.libero.it/foxes/MultiPrecision.htm -- Kind regards, Niek Otten &qu...

How do I add muitlple records through one form to a table
Any downloadable example would help greatly. Thanks. On Thu, 6 Dec 2007 17:08:00 -0800, miasma <miasma@discussions.microsoft.com> wrote: >Any downloadable example would help greatly. Thanks. Ummm... The Northwind sample database that comes with Access does this. In fact ANY Access database lets you add multiple records to a table via a Form. What specific problem are you having???? John W. Vinson [MVP] ...

can i input info in excell one page at a time instead of seeing .
i am new to using excell and my accountant has created a spead sheet for me , i was wondering can i set up a page so that the information i enter is done on its own page and then put in the speadsheet behind the scenes a bit like entering you contact details then it creates a address book Hi, Sure you can , you will need to be more specific on what you want to do, an example will help "barry" wrote: > i am new to using excell and my accountant has created a spead sheet for me , > i was wondering can i set up a page so that the information i enter is done > on its own ...

Returning the cell reference below
AHHHHHHHH!!!! This is really infuriating as I bet it's easy. Have an address formula which returns a cell reference. Great. What really need is the cell below that though, e.g. if it returns J2 I wan J3. Fiddled around with ROW, INDEX, nested ADDRESS statements...nothing. If anyone could help they would help an increasingly balding man. cheer -- Message posted from http://www.ExcelForum.com Hi Have a look at OFFSET() Andy "rudekid >" <<rudekid.12g932@excelforum-nospam.com> wrote in message news:rudekid.12g932@excelforum-nospam.com... > AHHHHHHHH!!!! T...

Extra text added by system when converting to PDF?
I've been using PDFMaker to convert my MS-Word styles to bookmarks for the past couple of years, but now that my office has upgraded us to Acrobat 8, I'm running into a problem. Each time a bookmark is created from a Word doc, there is a very small bit of text added to the front of it. The system seems to be numbering the bookmarks sequentially, starting with "0B" for the first bookmark, and continuing for the rest. They're very small characters, and unless you zoom in very closely, they look like periods at the front of each line. Example: .Bookma...