stack a range of columns in to 2 columns.

O

outlawdevil

Board Regular
Joined
Jun 30, 2009
Messages
238
Hi,

I need help on VBA code to stack a range of multiple columns into 2 columns. So far, I have only found vba to stack multiple columns into 1 column. please bear in mind that there are empty row with empty data as well and I want those to stack as well. I am only using 2 row x 2 column as example here but the range is over 100 rows+ and 84 columns. Thanks in advance!

Data looks like this

User apple User orange
User peach User Kiwi


I want it to look like

User apple
User peach
User orange
User Kiwi
 

Excel Facts

What does custom number format of ;;; mean?
Three semi-colons will hide the value in the cell. Although most people use white font instead.
Sort by date Sort by votes
How about
Code: 
Sub stackCols()

   Dim i As Long
   Dim UsdRws As Long
   
   UsdRws = Cells.find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row

   For i = 3 To Cells(1, Columns.Count).End(xlToLeft).Column Step 2
      Cells(1, i).Resize(UsdRws, 2).Copy Range("A" & Rows.Count).End(xlUp).Offset(1)
   Next i
   
End Sub
 
Upvote 0
Thanks fluff. I think it is not picking up every empty row and column within that range. Is there a way to select or define range within the code to make sure that every empty row and empty column are picked up.
 
Upvote 0
How about
Code: 
Sub stackCols()

   Dim i As Long
   Dim UsdRws As Long
   Dim Nxtrw As Long
   
   UsdRws = Cells.find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
   Nxtrw = UsdRws + 1
   For i = 3 To Cells(1, Columns.Count).End(xlToLeft).Column Step 2
      Cells(1, i).Resize(UsdRws, 2).Copy Range("A" & Nxtrw)
      Nxtrw = Nxtrw + UsdRws
   Next i
   
End Sub
 
Upvote 0
it is still not picking up the EF and GH column. the format looks like this

[TABLE="width: 1076"]
<tbody>[TR]
[TD="align: right"]1.00[/TD]
[TD]A[/TD]
[TD="align: right"]1.00[/TD]
[TD]B[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]2.00[/TD]
[TD]A[/TD]
[TD="align: right"]2.00[/TD]
[TD]B[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]3.00[/TD]
[TD]A[/TD]
[TD="align: right"]3.00[/TD]
[TD]B[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]4.00[/TD]
[TD]A[/TD]
[TD="align: right"]4.00[/TD]
[TD]B[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]5.00[/TD]
[TD]A[/TD]
[TD="align: right"]5.00[/TD]
[TD]B[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]6.00[/TD]
[TD]A[/TD]
[TD="align: right"]6.00[/TD]
[TD]B[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]1.00[/TD]
[TD]C[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]2.00[/TD]
[TD]C[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]3.00[/TD]
[TD]C[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]4.00[/TD]
[TD]C[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]5.00[/TD]
[TD]C[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]6.00[/TD]
[TD]C[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]1.00[/TD]
[TD]D[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]2.00[/TD]
[TD]D[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]3.00[/TD]
[TD]D[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]4.00[/TD]
[TD]D[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]5.00[/TD]
[TD]D[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]6.00[/TD]
[TD]D[/TD]
[/TR]
</tbody><colgroup><col><col><col><col><col><col><col><col></colgroup>[/TABLE]
 
Upvote 0
Ok, how about
Code: 
Sub stackCols()

   Dim i As Long
   Dim UsdRws As Long
   Dim UsdCols As Long
   Dim Nxtrw As Long
   
   UsdRws = Cells.find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
   UsdCols = Cells.find("*", SearchOrder:=xlByColumns, SearchDirection:=xlPrevious).Column
   Nxtrw = UsdRws + 1
   For i = 3 To Cells(1, UsdCols) Step 2
      Cells(1, i).Resize(UsdRws, 2).Copy Range("A" & Nxtrw)
      Nxtrw = Nxtrw + UsdRws
   Next i
   
End Sub
 
Upvote 0
Oops it should be
Code: 
   For i =[COLOR=#0000ff] 3 To UsdCols[/COLOR] Step 2
      Cells(1, i).Resize(UsdRws, 2).Copy Range("A" & Nxtrw)
      Nxtrw = Nxtrw + UsdRws
   Next i
 
Upvote 0
Glad to help & thanks for the feedback
 
Upvote 0
You must log in or register to reply here.

Similar threads

B
  • Solved
Textjoin and Filter using VBA
Replies
7
Views
413
BritsBlitz
B
L
  • Solved
Macro to Stack row of headers x times then next header xtimes in Column
Replies
7
Views
127
Joe4
Joe4
B
  • Solved
Table lookup and returning an answer based on a specific condition
Replies
5
Views
409
Peter_SSs
Peter_SSs
J
  • Question Question
Stacked Clustered Column Chart
Replies
0
Views
279
johnnypp
J
D
  • Solved
SUM of Columns based on multiple criteria with User Form
2
Replies
15
Views
368
Alex Blakenburg
A

Forum statistics

Threads
1,224,936
Messages
6,181,850
Members
453,068
Latest member
DCD1872

Share this page

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
Back
Top