Split Cells Into Multiple Rows using Vb Code

Question

As I am new to Vb excel. Can someone suggest me how I can Split Cells Into Multiple Rows using Vb Code. I have tried few code but not working.

Example data :

Answer 1

Here is an example to get you started.

This code will take the active cell you have highlighted and break up the string by spaces, expanding it across columns in row 1.

Example:

1. Cell A5 contains "Hello World Test"
2. Highlight cell A5
3. Call the sub

4. The code will execute and you will now have "Hello" in A1, "World" in A2, and "Test" in A3

   Sub Example()
       Dim txt As String
       Dim i As Integer
       Dim fullname As Variant
   
       txt = ActiveCell.Value
       fullname = Split(txt, " ")
       For i = 0 To UBound(fullname)
           Cells(1, i + 1).Value = fullname(i)
           MsgBox fullname(i)
       Next i
   End Sub