Yeah... for some reason it's too much data... not sure why. Try R! Or split it up into smaller pieces! :)

Yeah... for some reason it's too much data... not sure why. Try R! Or split it up into smaller pieces! :)

Thanks Amit, I will give it a try.

About the macro it comes with "Run-time Error '6', Overflow"

Thanks Amit, I will give it a try.

About the macro it comes with "Run-time Error '6', Overflow"

Hi, I am doing tables to list. Something over 40 columns and 500 rows it does not run anymore. So I keep splinting around this range. But I work with some tables over 200 columns and 1000 rows.

What do you mean "it doesn't run anymore"? If you're talking about the screen going blank temporarily, just let it... I feel like 20000 rows shouldn't be a challenge. But perhaps you know better. These days I do my transformations in R... much more efficient and the limit is in the millions of rows. Give it a try!

Hi, I am doing tables to list. Something over 40 columns and 500 rows it does not run anymore. So I keep splinting around this range. But I work with some tables over 200 columns and 1000 rows.

What do you mean "it doesn't run anymore"? If you're talking about the screen going blank temporarily, just let it... I feel like 20000 rows shouldn't be a challenge. But perhaps you know better. These days I do my transformations in R... much more efficient and the limit is in the millions of rows. Give it a try!

As someone said this macro saved my life. It's amazing.
But there is limit, if the range is too large it stops.

For i_ctr = 0 To i - 1

Glad your life has been saved :) are you doing lists->tables or tables->lists? How big are you talking?

As someone said this macro saved my life. It's amazing.
But there is limit, if the range is too large it stops.

For i_ctr = 0 To i - 1

Glad your life has been saved :) are you doing lists->tables or tables->lists? How big are you talking?

Thanks! This is perfect :)

Saved me a lot of time.

Thanks! This is perfect :)

Saved me a lot of time.

Hi there,
I'm trying to use the macro on a list of 100k+ rows but it freezes at row ~10k. I changed the variables i and i_ctr to long and also the array to arr(1000000, 5) but it still doesn't work. Any suggestion?

thanks

My suggestion? Upgrade and learn R. :D (Just kidding kind of). Um... are you going from List -> Table or other way around? When it freezes does it freeze and crash, or just freeze?

I've heard of R ... for some reason we don't/can't have it here.
I was finally able to get the thing working. Just had to be (very) patient. It took about an hour to complete the task. Not as fast as I hoped, but definitely faster than any other method we were considering!

Hi there,
I'm trying to use the macro on a list of 100k+ rows but it freezes at row ~10k. I changed the variables i and i_ctr to long and also the array to arr(1000000, 5) but it still doesn't work. Any suggestion?

thanks

My suggestion? Upgrade and learn R. :D (Just kidding kind of). Um... are you going from List -> Table or other way around? When it freezes does it freeze and crash, or just freeze?

I've heard of R ... for some reason we don't/can't have it here.
I was finally able to get the thing working. Just had to be (very) patient. It took about an hour to complete the task. Not as fast as I hoped, but definitely faster than any other method we were considering!

Just wanted to say THANKS!!!

:)

Just wanted to say THANKS!!!

:)

Hi, Great work there, Is there a possibility , That i can get code for only list to table. I am new to vba and spent few hours but couldn't figure it out. what to keep and what to remove . Any help would be appreciated

Thank You

I mean , I have done some manipulation in my data and made a list out of it, now i need to make it a table again, so for end to end automation , i need to define the ranges and not take it at runtime.

So in the beginning of the macro, i take the ranges.. You can see that i ask for them in the inputbox fields. Instead of asking for input, define the range there. Also, if you only need list to table, have you looked at pivot tables?

Hi, Great work there, Is there a possibility , That i can get code for only list to table. I am new to vba and spent few hours but couldn't figure it out. what to keep and what to remove . Any help would be appreciated

Thank You

I mean , I have done some manipulation in my data and made a list out of it, now i need to make it a table again, so for end to end automation , i need to define the ranges and not take it at runtime.

So in the beginning of the macro, i take the ranges.. You can see that i ask for them in the inputbox fields. Instead of asking for input, define the range there. Also, if you only need list to table, have you looked at pivot tables?

This macro is amazing...it was what I was looking for the last few months. Thanks!
I have a problem though, after using it to convert a 12x31 weather data table into 1x365 list for few years of data it crashed on an excel 2016. What should I do? FYI I download the macro from this site and used it without any change.

happy you like it! Were you trying to do all the years at the same time? Maybe you hit the bottom of the excel file? It would be helpful if you give me a screenshot of the error... we can start like that.

This macro is amazing...it was what I was looking for the last few months. Thanks!
I have a problem though, after using it to convert a 12x31 weather data table into 1x365 list for few years of data it crashed on an excel 2016. What should I do? FYI I download the macro from this site and used it without any change.

happy you like it! Were you trying to do all the years at the same time? Maybe you hit the bottom of the excel file? It would be helpful if you give me a screenshot of the error... we can start like that.

This looks great!
But only one question: Would it be possible to use text as data?
When I try this it gives me error.

hrm... I feel like I have done it before. Have you tried enabling and disabling all options? Also, sometimes if your text is more than 254 characters it might cause a problem. If none of that works, send me your file and I'll take a look.

I tried to index the text with numbers so I could replace it later with a lookup but the macro went into a loop.

If you like i will send you the file to try it out but I managed to do it with power query. Tnx for the help!

This looks great!
But only one question: Would it be possible to use text as data?
When I try this it gives me error.

hrm... I feel like I have done it before. Have you tried enabling and disabling all options? Also, sometimes if your text is more than 254 characters it might cause a problem. If none of that works, send me your file and I'll take a look.

I tried to index the text with numbers so I could replace it later with a lookup but the macro went into a loop.

If you like i will send you the file to try it out but I managed to do it with power query. Tnx for the help!

You did a wonderful job man! Just did a 180 x 50 matrix, it took him a while but it performed flawless!

Thanks!

Welcome :)

You did a wonderful job man! Just did a 180 x 50 matrix, it took him a while but it performed flawless!

Thanks!

Welcome :)

Hello,

This looks amazing but I am unsure how to get the macro into my personal workbook. Would you be able to give me some guidance?

Many thanks

Emma

Hrm. How come? Have you tried to open up the excel file and then dragged the userform to your personal?

Hello,

This looks amazing but I am unsure how to get the macro into my personal workbook. Would you be able to give me some guidance?

Many thanks

Emma

Hrm. How come? Have you tried to open up the excel file and then dragged the userform to your personal?

I love this. Exactly what I was looking for. But how can I get the macro to create the table in the same workbook and I select the cell to start the list from?
Also what is Me in your macro above?

Glad you like it :)

Instead of:

Workbooks.Add
Range("B2").Activate

Put:

Range("A1").Activate

where A1 is the range in your current file where it should start dumping out results. Be careful though, it might overwrite data. That's why I always start fresh... but as you wish. :)