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
59,101
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

Do you hate GETPIVOTDATA?
Prevent GETPIVOTDATA. Select inside a PivotTable. In the Analyze tab of the ribbon, open the dropown next to Options and turn it off

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
59,101
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,141,818
Messages
5,708,768
Members
421,588
Latest member
Wawie

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