Urgent help!!!! VBA

[pswarna]

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..

 

[Gord Dibben]

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

 

[Ron Rosenfeld]

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
====================

--ron