Macro or function to copy cell B to multiple rows and then populate cell A2 onwards

[avaris79]

Hi All,

I am new to this forum and excel macro.
I have data in column A where it is a single value & in column B where it is multiple values separated by ";".
Is there a way, using macro or function, to convert column B to multiple rows, then copy values from A1 to A+n onwards? Refer to my attached pictures for reference.
Thank you.

 

[mohadin]

Hi
And welcome
Try

Sub test()
    lr = Cells(Rows.Count, 2).End(xlUp).Row
    For i = lr To 2 Step -1
        x = Split(Cells(i, 2), ";")
        Cells(i, 1).Offset(1).Resize(UBound(x)).EntireRow.Insert
        Cells(i, 1).Offset(1).Resize(UBound(x) - 1) = Cells(i, 1)
        Cells(i, 2).Resize(UBound(x) + 1) = Application.Transpose(x)
    Next
End Sub

 

[avaris79]

Hi
And welcome
Try

Sub test()
    lr = Cells(Rows.Count, 2).End(xlUp).Row
    For i = lr To 2 Step -1
        x = Split(Cells(i, 2), ";")
        Cells(i, 1).Offset(1).Resize(UBound(x)).EntireRow.Insert
        Cells(i, 1).Offset(1).Resize(UBound(x) - 1) = Cells(i, 1)
        Cells(i, 2).Resize(UBound(x) + 1) = Application.Transpose(x)

[/QUOTE]

Thank you very much! 1 issue is that when there are more than 1 row in the source A1, the last value in A+n seems to be blank.

Also, how do we also built a function to copy value from column C & D to C+n & D+n, as shown in attachment.

 

[mohadin]

avaris79​

In you original pose #1
Your data was ended with << ; >>
In the above post there is non
What is the correct data layout you want

 

[avaris79]

avaris79​

In you original pose #1
Your data was ended with << ; >>
In the above post there is non
What is the correct data layout you want

Sorry didn't notice that, the data is supposed end with no ";" in column B.

 

[mohadin]

In this case
just
change

Cells(i, 1).Offset(1).Resize(UBound(x) - 1) = Cells(i, 1)

to

Cells(i, 1).Offset(1).Resize(UBound(x)) = Cells(i, 1)

 

[mohadin]

And

Sub test()
    lr = Cells(Rows.Count, 2).End(xlUp).Row
    For i = lr To 2 Step -1
        x = Split(Cells(i, 2), ";")
        Cells(i, 1).Offset(1).Resize(UBound(x)).EntireRow.Insert
        Cells(i, 1).Offset(1).Resize(UBound(x)) = Cells(i, 1)
        Cells(i, 2).Resize(UBound(x) + 1) = Application.Transpose(x)
        Cells(i, 3).Offset(1).Resize(UBound(x)) = Cells(i, 3)
        Cells(i, 4).Offset(1).Resize(UBound(x)) = Cells(i, 4)
    Next
End Sub

for the second issue

 

[avaris79]

Thank you very much Mohadin!

I would like to understand on statement "Cells(i, 1).Offset(1).Resize(UBound(x)).EntireRow.Insert". How do we intepret this statement? I understand until Offset, from Resize onwards i have no idea what it is doing. Appreciate if you can share the logic on this?
Thanks.

 

[mohadin]

well,
let say Cells(1,1) this Range("A1") Right?
Then Cells(1,1).Resize(rows,column) this means to expand the range down number of rows and to the right number of column
eg

Sub reszier()
    Cells(1, 1).Select
    MsgBox "Cells(1,1)  selected"
    Cells(1, 1).Resize(5, 4).Select
    MsgBox " from Cells(1,1) 5 rows down and 4 coulumns right selected"
End Sub

 

[avaris79]

well,
let say Cells(1,1) this Range("A1") Right?
Then Cells(1,1).Resize(rows,column) this means to expand the range down number of rows and to the right number of column
eg

Sub reszier()
    Cells(1, 1).Select
    MsgBox "Cells(1,1)  selected"
    Cells(1, 1).Resize(5, 4).Select
    MsgBox " from Cells(1,1) 5 rows down and 4 coulumns right selected"
End Sub

Ah i see it is similar to dragging the cursor from cell (1,1) and dragging it 5 rows down and 4 columns right. How about the Ubound(x) statement? In this case X = column B which is required to be split?