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!
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
58,379
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)
Good tip, Peter. Never thought of using Transpose with an array. That gets rid of one unnecessary loop.
Thanks.
 

Excel Facts

Return population for a City
If you have a list of cities in A2:A100, use Data, Geography. Then =A2.Population and copy down.

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
58,379
Office Version
  1. 365
Platform
  1. Windows
If we were to name the Macro or Process, what'd we name it? My heading is a bit generic!
I named it "MyInsertRows", but you can name it anything you want. So whatever makes the most sense to you.
 

Forum statistics

Threads
1,137,058
Messages
5,679,366
Members
419,822
Latest member
prashantakerkar

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