VBA code to resize data to a number of rows

[Momentman]

Let me try simplify

Can I get a VBA code to resize this data

The raw data is in columns A to C and has uneven number of rows but i want to transform to exactly 6 rows and as many columns as are necessary as in the data below

I also posted on mrexcel.com http://www.mrexcel.com/forum/excel-q...mber-rows.html

How do I post a workbook here, if at all I can

[jason.b75]

Welcome Momentman.

You can attach a workbook by editing your original post, or clicking the 'Go Advanced' button and looking for the paperclip icon above the input area.

I'll start writing up some code to do this for you, if you can provide a sample showing data source and expected output then it will help with testing.

[jason.b75]

See if this gives the right result

Please Login or Register  to view this content.

[Momentman]

See if this gives the right result

Very nicely done. It does give the right results and this is similar to what i was able to come up with

But i have in total over 6million data points, so looping from cell to cell could take a while, so i was thinking if there was a way to kind of avoid that and make it faster

[jason.b75]

I tested the code with around 250k cells of data and it took about 10 seconds, that was without setting screenupdating to false, etc.

I can think of ways to do it in 'blocks' but it would need a more cumbersome method, which might be slower, and may not necessarily give the desired output.

The theory.

Filter column A in the data source to show only the records that should be copied. Then copy the visible portion of column A to a new sheet.
Clear the filter and repeat for column B, C, etc.

Next work with the new sheet.

Assuming 20 rows specified, cut (not copy) rows 21:40 to next empty column in row 1, repeat for 41:60, etc. until end of data.

Depending on the number of records copied from each column in the original data source, you could end up with a lot of blanks, re-processing the data to tidy up again could make for a very inefficient process.