Solved: Cell Content into Different Rows

[krishhi]

Hello Guys,

I have a situation. I have data in a Cell with Enters. Let say

In "A2" I have data like

this is the first data
this is the second data
this is the third data

Here I want a macro to split the lines into rows

Like
In A2 : this is the first data
In A3 : this is the second data
in A4 : this is third data

Any clues?

Thanks in advance,

[Ago]

I cant manage to make a cell contain Enters, so i cant try it but this should in theroy work.
data should be an array that you can paste in each cell

data = split("A2", chr(13))

[krishhi]

@Ago

Thanks for the reply,

Actually i am a beginner of the VBA, So i am looking for the total solution.

Any Other Clues?

[mdmackillop]

Sub Macro1()

Dim t As Variant
Dim c As Range


Set c = Range("A2")
t = Split(c,Chr(10))
c.Resize(UBound(t)) = Application.Transpose(t)
End Sub

[krishhi]

Sub Macro1()

Dim t As Variant
Dim c As Range


Set c = Range("A2")
t = Split(c,Chr(10))
c.Resize(UBound(t)) = Application.Transpose(t)
End Sub



Thanks for the reply,
But If I have More than 4 Lines in a Cell, The macro is not working, It splits only first 4 lines and the deletes the Other lines.

could You please explain the terms

Ubound and Application.transpose

Thanks
Krrish

[mdmackillop]

Change this line which was omitting to copy the last item
c.Resize(UBound(t) + 1) = Application.Transpose(t)

Ubound is the number of items in an array, +1 because an array is zero based
Transpose turns from a "horizontal" to a "vertical" array

[krishhi]

Change this line which was omitting to copy the last item
c.Resize(UBound(t) + 1) = Application.Transpose(t)

Ubound is the number of items in an array, +1 because an array is zero based
Transpose turns from a "horizontal" to a "vertical" array

Wow .. Great. Now it is working.

Can you extend this a little bit more.

I have some data in other cells too, when i run this macro, It overwrites the next cells.

Anyway to paste the new data into new rows?

[mdmackillop]

Yes, but I don't know where to put them. Where are the "new rows"?

[krishhi]

Yes, but I don't know where to put them. Where are the "new rows"?


I have data in A,B,C, up to H Columns.

In B Column, we have the Data to split.

So, when it splits, the remaining Data in the Other columns should copied to the splited cells.

[mdmackillop]

Can you post a sample workbook. Nothing in the last refers to overwriting cells.

[krishhi]

Can you post a sample workbook. Nothing in the last refers to overwriting cells.


Yes Sure,

I have attached an Sample File, The Column "E" Is the data to split.

[mdmackillop]

Your sample doesn't match the description in Post 9, but this should work on your sample

Option Explicit

Sub Macro1()

Dim t As Variant
Dim c As Range
Dim Rws As Long

Set c = ActiveCell
t = Split(c, Chr(10))
Rws = UBound(t)
c.Offset(1, -4).Resize(Rws, 5).Insert shift:=xlDown
c.Resize(Rws + 1) = Application.Transpose(t)
c.Offset(, -4).Resize(Rws + 1, 4).FillDown

End Sub

[krishhi]

Your sample doesn't match the description in Post 9, but this should work on your sample

[/vba]


Thank you very much. It's works.

ya, I have some communication problem. :P.

Here my small gift for you :)

http://www.gadgets-reviews.com/uimg_new/OEE051-1216873348.jpg