| Home |
| Search |
| Today's Posts |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Convert Cell w/ Manual Breaks into New Rows
That worked perfectly! Thank you so very much!
-- Thanks "TomPl" wrote: This code assumes that the active cell is the first data cell in column A. I think it will do what you asked by inserting a row after each cell with a hard return and copying the appropriate data. Sub DoFunnyThings() Dim rngCell As Range Set rngCell = ActiveCell Do Until IsEmpty(rngCell) If InStr(rngCell, Chr(10)) 0 Then rngCell.Offset(1, 0).EntireRow.Insert rngCell.Offset(1, 0).Value = Mid(rngCell, InStr(rngCell, Chr(10)) + 1, 256) rngCell.Offset(1, 1).Value = rngCell.Offset(0, 1).Value rngCell.Value = Left(rngCell, InStr(rngCell, Chr(10)) - 1) End If Set rngCell = rngCell.Offset(1, 0) Loop End Sub "hr38581" wrote: I am attempting to develop a macro that creates a worksheet and pastes into it a range of data (2 columns) from another worksheet in the same workbook. (Ive got that much working so far in my macro. Now for the part I havent been able to figure out€¦) Column A will have some cells that contain manual line breaks in between the data items in that cell. The cells in Column B will only contain a single data item. The single data item in Column B needs to be associated with each of the data items in Column A, same row. So I need each cell (in Column A) that contains manual line break(s) to be broken into multiple rows and the value in the same row Column B repeated in each new row. (The columns will always be 2, but the rows will vary each time the macro is run.) Example: Start with this: Col A Col B Row1 data1.1 X Row2 data2.1 Y data2.2 data2.3 Row3 data3.1 Z data3.2 Change it to this: Col A Col B Row1 data1.1 X Row2 data2.1 Y Row3 data2.2 Y Row4 data2.3 Y Row5 data3.1 Z Row6 data3.2 Z What code can make this happen? -- Thanks much! |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Remove Manual Line Breaks in Cell | Excel Discussion (Misc queries) | |||
| Dealing with automatic / manual page breaks | Excel Discussion (Misc queries) | |||
| How do I insert manual line breaks in excel? | Excel Worksheet Functions | |||
| Remove all manual page breaks | Excel Programming | |||
| manual line breaks | Excel Discussion (Misc queries) | |||
Threaded Mode
