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
722 Views

Similar Articles

[PageSpeed] 42

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:

How to Convert UTC to localTIme(C# )
I have got the value of user account's lastlogon time. Its type is Int64. This value is stored as a large integer that represents the number of 100 nanosecond intervals since January 1, 1601 (UTC)(Refer to MSDN). I don't know how to convert this value to localTime. The following is my code. ################################################################ DirectoryEntry deUser = new DirectoryEntry(ldappath); DirectorySearcher src = new DirectorySearcher(deUser); src.Filter = "(&(objectClass=user)(SAMAccountName=" + accountNa...

Removal of thousands seperator in downloaded data
I have downloaded data with an open space as thousands seperator, an example is a number like 2 098,67. Excel will not recognize this as a number. How can I remove this thousands seperator so that the number becomes 2098,67? I need to do this to use the data in another program. See other post. -- HTH RP (remove nothere from the email address if mailing direct) "finney78" <finney78@discussions.microsoft.com> wrote in message news:CEE4BBB3-12AB-4DDD-AD0A-F88ADD242B73@microsoft.com... > I have downloaded data with an open space as thousands seperator, an example >...

Inserting a cell in the header.
We are trying to get a cell in the header of a sheet. Can this be done? Example would be in our sheet in D9 is TFF. We want whatever is in D9 to go in the header. I'm not sure this makes sense. Thanks Wendy You could do this with a macro linked to the worksheet_change event. Insert the following code into the macro sheet for the sheet with the cell if interest. Private Sub Worksheet_Change(ByVal Target As Range) For Each Cell In Target If Cell.Address = "$D$9" Then ActiveSheet.PageSetup.CenterHeader = Cell.Value End If Next Cell End Sub -- mrice Research Scientist with ...

Convert text to time value
I have a series of time values in a 'General' format. They are of the type: 184525 Which is 18:45:25 or 6:45:25 pm. A time which is am would be of the type: 12345 Which is 1:23:45 am. Is there a way to convert those 'General' values to an Excel serial so that I can figure out the difference between two times? I've seen a bunch of examples on the net, but none of those that I have found deal with this format that I can tell. Thanks. Hi try =--TEXT(A1,"00:00:00") -- Regards Frank Kabel Frankfurt, Germany "Andy" <amelton@gmail.com> schrieb...

how to convert date
Hi, I'm looking for some method to convert mail date, in format: eg. "Sun, 18 Sep 2005 20:57:08 +0200", to computer local time. I tried CTime but without resoults. m. Have you tried COleDateTime::ParseDateTime()? m.wski21.usunto@aust.com wrote: > Hi, > > I'm looking for some method to convert mail date, in format: > eg. "Sun, 18 Sep 2005 20:57:08 +0200", to computer local time. > I tried CTime but without resoults. > > m. >I'm looking for some method to convert mail date, in format: >eg. "Sun, 18 Sep 2005 20:57:08 +0200&qu...

Excel: Auto converting text to numbers
I am downloading an Excel sheet, and the numbers come in as text. It basically comes in as "33 %" but Excel registers this as text, not a percentage. I have a cell that will be used to add the numbers, but since they are text it doesn't work. Given this information, is there a way to convert the imported data into numbers. I would prefer to include this into my formula. The potential numbers are: 0, 1, 2, 3, 4, 0 %, 25 %, 33 %, 50 %, 67 %, 75 %, 100 %, and N/A I would prefer a function, again if possible, that could convert any number. Please note, the space between the nu...

Multiple inputs one output
What could I use to enable one cell to have a value that is controlled by multiple options, but only one of them will be filled in but will vary each time. For instance, A1,A2,A3 are amounts of something that will be filled in and variable. Only one will be entered. I need the amount in A1,A2,or A3 to then be entered into G12 on another worksheet. Thanks for any help. Quick and dirty method (Assumes that one one of the three fields will have input, otherwise it will show all of the data entered) In cell G12 enter =VALUE(A1&B1&C1) if it is going to be text then enter =(A1&B...

publisher: 4 Millions Domains data with Category
Successfull Internet and Direct Marketing products on www.promotionsite.net * NEW * DOMUS Domains Toolkit Fall 2004 - Unique on the Net 4 Millions "Whois" Domains data with Expiration Date and Category*. Ultimate Version (October 2004) - Our best rate starts from US *$149*. A wonderful tool for Internet and Direct Marketing. Available in Basic, Advanced and Full Editions. It contains a domain database with 4 millions *FRESH* October 2004 New records .com, .net, .org. 4 Gigabytes MS Excel data zipped on CD-Roms/Dvd. Compression 3:1. MS Excel or Text tab del...

Auto filter hidden cells
Hello, Yesterday I created a table and by using the custom format ;;; I managed to hide all but the first line of each category and still get all lines of the same instance to show in an auto filter. Today I open the sheet and try to use the auto filter. All I get now is the visible line only. eg Yesterday I made: Bob 1 Bob 2 Bob 3 Look Like Bob 1 2 3 Today I now only get Bob 1 No edits have been made whatsoever. How can I get it back? I really need this back as I want my print format to hide the repetitions but the auto filter to still be fully...

My follow up folder has disappeared after one use.
I'm using Outlook that came in the Office Pro 2003 package. I used the FOLLOW UP search folder in the "Favorites" window... to hold one message... 24 hours later I handled that matter... and took the message away to file in a place where I can find it... the folder also disappeared. Next I tried to replace the folder by creating a new one... now (next day) no follow-up folder. Help is complex -- takes too long (for me) more demos would be helpful. In news:99DC2D96-3646-4C23-9DA3-EED57E8F0CA4@microsoft.com, JEBCOINC <JEBCOINC@discussions.microsoft.com> typed: > ...

(Publisher 2003) How do I get the two pages to print on to one pag
I need 2 pages to print on one page. I did see on a document that someone else made on my computer that the page indicators (at the bottom left; to switch between the two pages) that the folded part of the 2nd page was turned a different way, and I know that's exactly what I need to do, but I'm not sure how to. Help please? Thanks! Vampressjaden In Publisher 2000 (may be the same for other versions) File Page Setup Special fold Horizontal It will ask you if you want 4 pages - yes -- Don Vancouver, USA "Vampressjaden" <Vampressjaden@discussions.micros...

How do I convert dates to text keeping the format?
I'm trying to convert a column of data in date format *m/d/yyyy to a text format without converting to serial numbers. Ie: I want to retain the mm/dd/yyyy format. Is there a way to do this? =TEXT(A1,"MM/DD"/YYYY") "sprlarry" <sprlarry@discussions.microsoft.com> wrote in message news:69669AA6-FD15-47D7-843D-FC768728BF7A@microsoft.com... > I'm trying to convert a column of data in date format *m/d/yyyy to a text > format without converting to serial numbers. Ie: I want to retain the > mm/dd/yyyy format. Is there a way to do this? That ...

1 Cell 2 Values?
Hello All, Is it possible in excel to have it return two values in one cell separated by a comma and spaces? For example I want it to count the number of times in a month the value was positive and the number of times a value was negative and in the cell I want it to show A1: +, - Is this possible? Thanks to all, Shhhh Shhhh wrote... >Is it possible in excel to have it return two values in one cell separated >by a comma and spaces? > >For example I want it to count the number of times in a month the value was >positive and the number of times a value was negative a...

Cell Formats (like StyleSheets)
Version: 2008 Operating System: Mac OS X 10.5 (Leopard) Processor: Intel I cannot find the way to store some characteristics (font, size, ....) of cell-formatting to own formats (like stylesheets, for reusing them) <br> There are just 6 standards and I cannot add more. <br><br>Any ideas? Sure you can :-) Where are you trying? The easiest way is to format a cell the way you want the style configured then while the cell is selected go to Format> Style. Just type the name you want the style to have then click 'OK'. Alternatively, go to Format> S...

how to convert excel's .cvf file to .csv file
...

Graph and Cell References
I am looking to create a graph, that references cells from two different worksheets for the same data set. I would use part of one of the sheets (or a third possibly hidden sheet) to consolidate the data with formulas such as =Sheet1!A5 and =Sheet2!G2 Now make the charts anywhere you wish from the consolidated data. best wishes -- Bernard Liengme www.stfx.ca/people/bliengme remove CAPS in email address "balandino" <balandino@discussions.microsoft.com> wrote in message news:CF079347-7492-4BD9-9A3D-A483F5B1E962@microsoft.com... >I am looking to create a graph, that r...

SOP data entry / setup issue
Make the allow price overrides to only effect the unit price. The allow price markdowns would prevent them from entering markdowns. This would allow users to block unit price changes but still enter markdowns. ---------------- 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 suggestion in the Microsoft Web-based Newsreader and then click "I Agree" in the message pane. http:...

Convert Access97 to 2000
Hello, we're currently running access97 and would like to convert it to 2000, but we don't know what is the administrator password for this database. Also this database is running on multi user and have difference permission for diffence users. Could someone help me how to do make this happen but keep the currently permission retaint. Thanks ...

Data in pivot table not mirroring with data in source
I created a pivot table and since I was not able to protected, I inavertly typed some data into one of the cells in the pivot table, since then every time I enter a number in the source of the pivot table the pivot table now gives me the wrong number. Per example in one of the cells of the pivot table I change the number 3 for the number 4.10 now every time I type the number 3 in the table source, the pivot table writes 4.10, in any cell that contains the numeral 3. Is there a way to revert back? I try by typing the number 3 into the pivot table to match with the source and it seams to work, b...

Cannot display all datas in Line Chart
Hi everybody, I have a chart report , the type is line chart. It shows the values per half hour. the X axis is time (Per Half Hour) , the Y axis is the value for that time. it works well when I select the time between 130 Days , but it wont show the more than 130 days. eg : when I choose to show the data from Oct 22, 2007 to Feb 22, 2008 it shows the data from Oct 22,2007 to Feb 02 , 2008 I cannot get the accurity end data but I can guess it from the values in X axis the values are : Oct 22, Oct 26, Oct 31... ...Jan 22 , Jan 24 , Jan 27 , Jan 29 Please if some one can tell me does the...

Convert 2000 Calendar to web page
Greetings, When I convert my calendar for 2005 to a webpage, the page is off by 1 day. Is there a template or fix available to fix this? Thanks, Duane I can edit the html file but this should not be the case. Fixes? Suggestions...other than use Apple? "Duane Perry" <dlp_sr@yahoo.com> wrote in message news:yZRtd.5561$0r.1710@newsread1.news.pas.earthlink.net... > Greetings, > > When I convert my calendar for 2005 to a webpage, the page is off by 1 day. > Is there a template or fix available to fix this? > > Thanks, > > Duane > > Duane, ...

Getting an integer value (in one line) out of SelectSingleNode from an XMLDoc.
[C# code oriented] Two questions: (1) I can pull the text of an XML element as a string just fine using code as such: strSomeString = myXmlDoc.SelectSingleNode("/Element1/Element2/Element3[1]", myXmlNSMgr).InnerText; However, the text to be found there is an integer number. I have tried to use just about every casting and conversion method to be able to place that value into an integer variable. This includes code such as: intSomeInt = (int)(strSomeString = myXmlDoc.SelectSingleNode("/Element1/Element2/Element3[1]", myXmlNSMgr).InnerText); intSomeInt = Convert.ToInt...

XML Note convert to DataSet
Hello, I have this function: object acmResponse = acmLogin.acmString("4001", "", paramFormLogin + paramUserBasics);System.Xml.XmlNode[] acmNodes = (System.Xml.XmlNode[])acmResponse; What I have todo, to convert the XML Object in the DataSet Object? Thank you Matthias ...

Convert
Is it possible to convert a Money file created in the USA version to that of the UK version? Thanks in advance The general way is QIF Export then Import. It's involved and has limitations like loan accounts don't QIF. See http://www.bollar.org/msmoney/#Q1. "Crispy" <nowayspammers@hotmail.com> wrote in message news:uQKSfzfyDHA.2500@TK2MSFTNGP09.phx.gbl... > Is it possible to convert a Money file created in the USA version to that of > the UK version? ...

How do I combine two worksheets into one graph
I have two worksheets on an Excel spreadsheet. I would like to take both worksheets and place in one graph. Any suggestions? Make a chart with data from one sheet. Then copy the data on the other sheet, select the chart, and use Paste Special from the Edit menu to add the copied data as New Series. - Jon ------- Jon Peltier, Microsoft Excel MVP Peltier Technical Services Tutorials and Custom Solutions http://PeltierTech.com/ _______ McPowerUser wrote: > I have two worksheets on an Excel spreadsheet. I would like to take both > worksheets and place in one graph. Any suggestions? ...