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

[modiria50989]

Hello please help me with this:
I have this:

apple	red	20 max
orange	green	34 min
banana	yellow	56 max
berry	black	76 min
watermelon	yellow	40 min

<tbody>
</tbody>

I want this:

apple

red

20 max

orange

green

34 min

banana

yellow

56 max

berry

black

76 min

watermelon

yellow

40 min

<tbody>
</tbody>

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