how to put each corresponding column in rows to get 1 column-multiple rows?

[modiria50989]

Hello please help me with this:
I have this:

[TABLE="class: grid, width: 100, align: center"]
<tbody>[TR]
[TD]apple[/TD]
[TD]red[/TD]
[TD]20 max[/TD]
[/TR]
[TR]
[TD]orange[/TD]
[TD]green[/TD]
[TD]34 min[/TD]
[/TR]
[TR]
[TD]banana[/TD]
[TD]yellow[/TD]
[TD]56 max[/TD]
[/TR]
[TR]
[TD]berry[/TD]
[TD]black[/TD]
[TD]76 min[/TD]
[/TR]
[TR]
[TD]watermelon[/TD]
[TD]yellow[/TD]
[TD]40 min[/TD]
[/TR]
</tbody>[/TABLE]
I want this:

[TABLE="class: grid, width: 100, align: center"]
<tbody>[TR]
[TD]apple[/TD]
[/TR]
[TR]
[TD]red[/TD]
[/TR]
[TR]
[TD]20 max[/TD]
[/TR]
[TR]
[TD][/TD]
[/TR]
[TR]
[TD]orange
[/TD]
[/TR]
[TR]
[TD]green[/TD]
[/TR]
[TR]
[TD]34 min[/TD]
[/TR]
[TR]
[TD][/TD]
[/TR]
[TR]
[TD]banana[/TD]
[/TR]
[TR]
[TD]yellow[/TD]
[/TR]
[TR]
[TD]56 max[/TD]
[/TR]
[TR]
[TD][/TD]
[/TR]
[TR]
[TD]berry[/TD]
[/TR]
[TR]
[TD]black[/TD]
[/TR]
[TR]
[TD]76 min[/TD]
[/TR]
[TR]
[TD][/TD]
[/TR]
[TR]
[TD]watermelon[/TD]
[/TR]
[TR]
[TD]yellow[/TD]
[/TR]
[TR]
[TD]40 min[/TD]
[/TR]
[TR]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
Thank you.

 

[sheetspread]

This will work:

https://www.youtube.com/watch?v=xmqTN0X-AgY

Just number the rows and columns first

 

[MickG]

Try this with Data from active sheet, to Results on sheet2 column "A".

[COLOR="Navy"]Sub[/COLOR] MG07Jun49
[COLOR="Navy"]Dim[/COLOR] Rng [COLOR="Navy"]As[/COLOR] Range, Dn [COLOR="Navy"]As[/COLOR] Range, c [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long[/COLOR]
[COLOR="Navy"]Set[/COLOR] Rng = ActiveSheet.Cells(1).CurrentRegion.Resize(, 3)
c = 1
[COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] Dn [COLOR="Navy"]In[/COLOR] Rng.Rows
    Sheets("Sheet2").Cells(c, 1).Resize(Rng.Columns.Count) = Application.Transpose(Dn.Value)
    c = c + Rng.Columns.Count + 1
[COLOR="Navy"]Next[/COLOR] Dn
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

Regards Mick

 

[modiria50989]

Thank you for your code but i get an error that says, Run-time error 9: Subscript out of range.

Try this with Data from active sheet, to Results on sheet2 column "A".

[COLOR=Navy]Sub[/COLOR] MG07Jun49
[COLOR=Navy]Dim[/COLOR] Rng [COLOR=Navy]As[/COLOR] Range, Dn [COLOR=Navy]As[/COLOR] Range, c [COLOR=Navy]As[/COLOR] [COLOR=Navy]Long[/COLOR]
[COLOR=Navy]Set[/COLOR] Rng = ActiveSheet.Cells(1).CurrentRegion.Resize(, 3)
c = 1
[COLOR=Navy]For[/COLOR] [COLOR=Navy]Each[/COLOR] Dn [COLOR=Navy]In[/COLOR] Rng.Rows
    Sheets("Sheet2").Cells(c, 1).Resize(Rng.Columns.Count) = Application.Transpose(Dn.Value)
    c = c + Rng.Columns.Count + 1
[COLOR=Navy]Next[/COLOR] Dn
[COLOR=Navy]End[/COLOR] [COLOR=Navy]Sub[/COLOR]

Regards Mick

 

[MickG]

Have a look at this example file:-
https://app.box.com/s/uo999gchyjthl2br9wv0qvp48uejpln5

 

[modiria50989]

This works great thank you! One more question please. In reality some of the rows have more than 3 columns. For example if I have a 4 column and a 6 column row then I have to change "3" to "6" in your code (...CurrentRegion.Resize(, 3)), but at the end I see more empty rows (instead of just one empty row) between each group. Is it possible to make sure I have only one empty row between each group?

I hope I could be clear on my question.
Thank you again.

Have a look at this example file:-
https://app.box.com/s/uo999gchyjthl2br9wv0qvp48uejpln5

 

[MickG]

Try this, It should cater for various numbers of columns with data.

[COLOR="Navy"]Sub[/COLOR] MG09Jun40
[COLOR="Navy"]Dim[/COLOR] Rng [COLOR="Navy"]As[/COLOR] Range, Dn [COLOR="Navy"]As[/COLOR] Range, c [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long,[/COLOR] cols [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long[/COLOR]
[COLOR="Navy"]Set[/COLOR] Rng = ActiveSheet.Cells(1).CurrentRegion
c = 1
[COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] Dn [COLOR="Navy"]In[/COLOR] Rng.Rows
    cols = Application.CountA(Dn)
    Sheets("Sheet2").Cells(c, 1).Resize(cols) = Application.Transpose(Dn.Value)
    c = c + cols + 1
[COLOR="Navy"]Next[/COLOR] Dn
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

Regards Mick

 

[modiria50989]

I'm getting run time error '9' on this line:
Sheets("Sheet2").Cells(c, 1).Resize(cols) = Application.Transpose(Dn.Value)

Try this, It should cater for various numbers of columns with data.

[COLOR=Navy]Sub[/COLOR] MG09Jun40
[COLOR=Navy]Dim[/COLOR] Rng [COLOR=Navy]As[/COLOR] Range, Dn [COLOR=Navy]As[/COLOR] Range, c [COLOR=Navy]As[/COLOR] [COLOR=Navy]Long,[/COLOR] cols [COLOR=Navy]As[/COLOR] [COLOR=Navy]Long[/COLOR]
[COLOR=Navy]Set[/COLOR] Rng = ActiveSheet.Cells(1).CurrentRegion
c = 1
[COLOR=Navy]For[/COLOR] [COLOR=Navy]Each[/COLOR] Dn [COLOR=Navy]In[/COLOR] Rng.Rows
    cols = Application.CountA(Dn)
    Sheets("Sheet2").Cells(c, 1).Resize(cols) = Application.Transpose(Dn.Value)
    c = c + cols + 1
[COLOR=Navy]Next[/COLOR] Dn
[COLOR=Navy]End[/COLOR] [COLOR=Navy]Sub[/COLOR]

Regards Mick

 

[MickG]

Have you got a Sheet named "Sheet2" in your workbook ????

 

[modiria50989]

Wait, actually works very well! thank you!
Instead of copy paste your code I typed that out and worked. Thanks again, you're awesome!

Try this, It should cater for various numbers of columns with data.

[COLOR=Navy]Sub[/COLOR] MG09Jun40
[COLOR=Navy]Dim[/COLOR] Rng [COLOR=Navy]As[/COLOR] Range, Dn [COLOR=Navy]As[/COLOR] Range, c [COLOR=Navy]As[/COLOR] [COLOR=Navy]Long,[/COLOR] cols [COLOR=Navy]As[/COLOR] [COLOR=Navy]Long[/COLOR]
[COLOR=Navy]Set[/COLOR] Rng = ActiveSheet.Cells(1).CurrentRegion
c = 1
[COLOR=Navy]For[/COLOR] [COLOR=Navy]Each[/COLOR] Dn [COLOR=Navy]In[/COLOR] Rng.Rows
    cols = Application.CountA(Dn)
    Sheets("Sheet2").Cells(c, 1).Resize(cols) = Application.Transpose(Dn.Value)
    c = c + cols + 1
[COLOR=Navy]Next[/COLOR] Dn
[COLOR=Navy]End[/COLOR] [COLOR=Navy]Sub[/COLOR]

Regards Mick