Urgent help!!!! VBA
If the cell(1,1) has data of several lines
eg: Hello World Hello World Hello World Hello World Hello World How can I split this data into several rows using VBA? The steps to do the same manually are 1. Select the data in cell(1,1) 2. Cut it 3. select cell(2,1) and select back cell(1,1)( to resize the cell1) 4. Paste the data in the buffer and excel automatically pastes each line into a different row. How do I perform the same using VBA, if I try to record the actions described above , excel is not recording any statements for steps 1 and 2. I appreciate any help.. --- Message posted from http://www.ExcelForum.com/ |
Urgent help!!!! VBA
Problem with recording these steps is that steps 1 and 2 are done in Edit
mode. VBA macro will not record or work while in Edit Mode. You could record splitting the data into 5 columns using the Text to Columns Wizard then transposing to a column. Line-break character(other) in Text Wizard De-limiter is usually ALT + 010 on Numpad. Hang in there. One of the VBA gurus will be along with something easier and more elegant. Gord Dibben Excel MVP On Thu, 11 Dec 2003 18:40:47 -0600, pswarna wrote: If the cell(1,1) has data of several lines eg: Hello World Hello World Hello World Hello World Hello World How can I split this data into several rows using VBA? The steps to do the same manually are 1. Select the data in cell(1,1) 2. Cut it 3. select cell(2,1) and select back cell(1,1)( to resize the cell1) 4. Paste the data in the buffer and excel automatically pastes each line into a different row. How do I perform the same using VBA, if I try to record the actions described above , excel is not recording any statements for steps 1 and 2. I appreciate any help.. --- Message posted from http://www.ExcelForum.com/ |
Urgent help!!!! VBA
On Thu, 11 Dec 2003 18:40:47 -0600, pswarna
wrote: If the cell(1,1) has data of several lines eg: Hello World Hello World Hello World Hello World Hello World How can I split this data into several rows using VBA? The steps to do the same manually are 1. Select the data in cell(1,1) 2. Cut it 3. select cell(2,1) and select back cell(1,1)( to resize the cell1) 4. Paste the data in the buffer and excel automatically pastes each line into a different row. How do I perform the same using VBA, if I try to record the actions described above , excel is not recording any statements for steps 1 and 2. I appreciate any help.. Assuming there is a Line Feed between each Hello World (as there is in your post), and also if you have a later version of VBA, then the following VBA routine will work: ======================== Option Explicit Sub SplitCell() Dim temp As Variant Dim i As Integer temp = Split(Cells(1, 1), vbLf) For i = 0 To UBound(temp) Cells(i + 1, 1) = temp(i) Next i End Sub ==================== --- Message posted from http://www.ExcelForum.com/ --ron |
All times are GMT +1. The time now is 02:48 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com