Macro to get data in the below format

Rev T

New Member
Joined
Jun 3, 2021
Messages
5
Office Version
  1. 2019
Platform
  1. Windows
I have data in the below format -

Col A1Col A2Col A3Col A4Col A5Col A6Col A7Col A8Col A9Col A10Col A11Col A12Col A13Col A14Col A15Col A16Col A17Col A18
ABC1,2,3,4,5,6,7,8DEFGHIJKLMNOPQ

I need it to be this way -

Col A1Col A2Col A3Col A4Col A5Col A6Col A7Col A8Col A9Col A10Col A11Col A12Col A13Col A14Col A15Col A16Col A17Col A18
ABC1DEFGHIJKLMNOPQ
ABC2DEFGHIJKLMNOPQ
ABC3DEFGHIJKLMNOPQ
ABC4DEFGHIJKLMNOPQ
ABC5DEFGHIJKLMNOPQ
ABC6DEFGHIJKLMNOPQ
ABC7DEFGHIJKLMNOPQ
ABC8DEFGHIJKLMNOPQ

Can someone help me out on this, thanks!
 

Excel Facts

VLOOKUP to Left?
Use =VLOOKUP(A2,CHOOSE({1,2},$Z$1:$Z$99,$Y$1:$Y$99),2,False) to lookup Y values to left of Z values.

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
58,379
Office Version
  1. 365
Platform
  1. Windows
Welcome to the Board!

So, is your value in column "A4" really column D on your worksheet?
The columns typically go in A, B, C, D, etc order, and rows go in 1,2,3,4, order.

What cell does the first line of data start in?
Is there a title row, so it is in cell A2, or is it in A1?
 

Rev T

New Member
Joined
Jun 3, 2021
Messages
5
Office Version
  1. 2019
Platform
  1. Windows
So, is your value in column "A4" really column D on your worksheet? - Yes
What cell does the first line of data start in? - A2
Is there a title row, so it is in cell A2, or is it in A1? - Yes, there's a title row and it's in A1
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
58,379
Office Version
  1. 365
Platform
  1. Windows
One last question I forgot to ask.
Is there always just one single row of data that you need to do this too, or might there be multiple rows that need to be expanded like this?
 

Rev T

New Member
Joined
Jun 3, 2021
Messages
5
Office Version
  1. 2019
Platform
  1. Windows

ADVERTISEMENT

There are multiple rows and it can be anywhere between 2 and 100 values separated by a comma in column D
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
58,379
Office Version
  1. 365
Platform
  1. Windows
A bit of a "brute force" method, but it works:
VBA Code:
Sub MyInsertRows()

    Dim lr As Long
    Dim r As Long
    Dim arr() As String
    Dim nr As Long
    Dim i As Long
    
    Application.ScreenUpdating = False
    
'   Find last row in column D with data
    lr = Cells(Rows.Count, "D").End(xlUp).Row
    
'   Loop through all rows backwards
    For r = lr To 2 Step -1
'       Get value from column D and split
        arr = Split(Cells(r, "D"), ",")
'       See if we need to insert any rows
        nr = UBound(arr)
        If nr > 0 Then
'           Insert new rows
            Rows(r + 1 & ":" & r + nr).Insert
'           Copy row info to new rows
            Rows(r).Copy Rows(r + 1 & ":" & r + nr)
'           Populate column D
            For i = LBound(arr) To UBound(arr)
                Cells(r + i, "D") = arr(i)
            Next i
        End If
    Next r

    Application.ScreenUpdating = True
    
End Sub
 
Solution

Rev T

New Member
Joined
Jun 3, 2021
Messages
5
Office Version
  1. 2019
Platform
  1. Windows

ADVERTISEMENT

Joe4,​

It worked :)
Thanks a ton!
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
58,379
Office Version
  1. 365
Platform
  1. Windows
You are welcome.
Glad I was able to help!
:)
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
49,513
Office Version
  1. 365
Platform
  1. Windows
@Joe4 @Rev T
I would suggest one small change and that is to write arr to the worksheet all at once instead of a cell at a time.

Rich (BB code):
For i = LBound(arr) To UBound(arr)
    Cells(r + i, "D") = arr(i)
Next i
Cells(r, "D").Resize(nr + 1).Value = Application.Transpose(arr)
 

Rev T

New Member
Joined
Jun 3, 2021
Messages
5
Office Version
  1. 2019
Platform
  1. Windows
@Joe4 @Peter_SSs

Thanks once again.

If we were to name the Macro or Process, what'd we name it? My heading is a bit generic!
 

Forum statistics

Threads
1,137,063
Messages
5,679,401
Members
419,825
Latest member
MegastarMagus

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top