Converting horizontal list to vertical - VBA

[mhaidar81]

Hello all,

I am looking for a macro that would convert a horizontal list to a vertical one. All I can come up with is a macro to copy the row and paste it X number of times, but my list contains thousands of rows and doesn't seem very practical. Assuming ID column is column A:


[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]

A
---​

ID[/TD]
[TD]

B
---​

Project[/TD]
[TD]

C
---​

Rate[/TD]
[TD="align: center"]D
---
Project A Count[/TD]
[TD="align: center"]E
---
Project B Count[/TD]
[TD="align: center"]F
---
Project C Count[/TD]
[TD]

G
---
​

Total[/TD]
[TD]

H
---​

Rate[/TD]
[TD="align: center"]I
---
Project D Count[/TD]
[TD="align: center"]J
---
Project E Count[/TD]
[TD="align: center"]K
---
Project D Count[/TD]
[TD]

L
---
​

Total[/TD]
[/TR]
[TR]
[TD="align: center"]1001[/TD]
[TD="align: center"]12001.01[/TD]
[TD="align: center"]$50[/TD]
[TD="align: center"]3[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]5[/TD]
[TD="align: center"]400[/TD]
[TD="align: center"]60[/TD]
[TD="align: center"]6[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]420[/TD]
[/TR]
</tbody>[/TABLE]

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]ID[/TD]
[TD]Project[/TD]
[TD]Project Name[/TD]
[TD]Rate[/TD]
[TD]Count[/TD]
[TD]Total[/TD]
[/TR]
[TR]
[TD]1001[/TD]
[TD]Project A[/TD]
[TD]12001.01[/TD]
[TD]$50[/TD]
[TD]3[/TD]
[TD]150[/TD]
[/TR]
[TR]
[TD]1001[/TD]
[TD]Project B[/TD]
[TD]12001.01[/TD]
[TD]$50[/TD]
[TD]0[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]1001[/TD]
[TD]Project C[/TD]
[TD]12001.01[/TD]
[TD]$50[/TD]
[TD]5[/TD]
[TD]250[/TD]
[/TR]
[TR]
[TD]1001[/TD]
[TD]Project D[/TD]
[TD]FR0006[/TD]
[TD]$60[/TD]
[TD]6[/TD]
[TD]360[/TD]
[/TR]
[TR]
[TD]1001[/TD]
[TD]Project E[/TD]
[TD]FR0003[/TD]
[TD]$60[/TD]
[TD]1[/TD]
[TD]60[/TD]
[/TR]
[TR]
[TD]1001[/TD]
[TD]Project F[/TD]
[TD]FR0005[/TD]
[TD]$60[/TD]
[TD]0[/TD]
[TD]0[/TD]
[/TR]
</tbody>[/TABLE]

After the list is converted:

• Project A, Project B, and Project C should always populate “Project Name” with the value from Column B
• Project D should populate “Project Name” with the value “FR0006”
• Project E should populate “Project Name” with the value “FR0003”
• Project F should populate “Project Name” with the value “FR0005”

I would appreciate any help with this

 

[mhaidar81]

Hey guys, I have a follow-up question on this. Let's say that there are additional columns within my sheet that contain useless information and I have no interest in converting them into the vertical format. Can I select the columns that I'm only interested in? The number of columns will be fixed in the sheet.

 

[mhaidar81]

Never mind, I figured it out!

Hey guys, I have a follow-up question on this. Let's say that there are additional columns within my sheet that contain useless information and I have no interest in converting them into the vertical format. Can I select the columns that I'm only interested in? The number of columns will be fixed in the sheet.

 

[mse330]

You can do that in my code by changing the below

Set Rg = ActiveSheet.Range("A1").CurrentRegion '<--- Comment/delete this line

Set Rg = ActiveSheet.Range("A1", Range("L" & Rows.Count).End(xlUp))  '<--- Replace with this line