Macro to get data in the below format

[Rev T]

I have data in the below format -

Col A1

Col A2

Col A3

Col A4

Col A5

Col A6

Col A7

Col A8

Col A9

Col A10

Col A11

Col A12

Col A13

Col A14

Col A15

Col A16

Col A17

Col A18

A

B

C

1,2,3,4,5,6,7,8

D

E

F

G

H

I

J

K

L

M

N

O

P

Q

I need it to be this way -

Col A1

Col A2

Col A3

Col A4

Col A5

Col A6

Col A7

Col A8

Col A9

Col A10

Col A11

Col A12

Col A13

Col A14

Col A15

Col A16

Col A17

Col A18

A

B

C

1

D

E

F

G

H

I

J

K

L

M

N

O

P

Q

A

B

C

2

D

E

F

G

H

I

J

K

L

M

N

O

P

Q

A

B

C

3

D

E

F

G

H

I

J

K

L

M

N

O

P

Q

A

B

C

4

D

E

F

G

H

I

J

K

L

M

N

O

P

Q

A

B

C

5

D

E

F

G

H

I

J

K

L

M

N

O

P

Q

A

B

C

6

D

E

F

G

H

I

J

K

L

M

N

O

P

Q

A

B

C

7

D

E

F

G

H

I

J

K

L

M

N

O

P

Q

A

B

C

8

D

E

F

G

H

I

J

K

L

M

N

O

P

Q

Can someone help me out on this, thanks!

 

[Joe4]

@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.

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.

 

[Joe4]

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.