Combine multiple sets of columns into a singe set of columns

Hi all,

Long time lurker, first time poster here.

I have a spreadsheet with columns of data in A-B-C for a dataset I need to analyze. Then, the same headers are repeated in columns E-F-G, and there is a blank column between these data sets in column D. This same format of headers are repeated again for 100s of columns, and this goes on for 100s of rows. It's a gant repeating dataset, and I'm looking to mere it all together into 3 columns.

Example:

Columns go Date-Tag-Value-Blank-Date-Tag-Value-Blank-Date-Tag-Value, etc, etc.

End result would be all the data merged together as Date-Tag-Value. The first row are headers.

Can anyone help with some VBA here? The best I have found was the following, but it appears broken.

Please Login or Register  to view this content.

Lee,
Thank you very much !!!

Works beautifully! You made my day, great start to the weekend.

Happy to help, thank you for the rep!

Hello again,

So I appear to have run into a wall. My intent was to analyze this data in a pivot table, however I may have reached the limits of the excel sheet. I am not sure.
The above macro errors, telling my I cannot copy and paste due to different area sizes.

Running the macro on a smaller dataset works beautifully. Can anyone help me with this?

I have attached a link the file here.

I was thinking, if this problem is indeed due to Excel's number of row limitations, can the VBA code be modified to consolidate every 100th row?

In the example, data is recorded every 5 seconds, however I only really need an average so every nth row can be used to plot and study.

I have no idea how to modify it to grab every 100th row and merge. Can any one help out ?

Much appreciated

Yes, I can do that. Give me 45 minutes. Also, I'm curious. Select your worksheet and press CTRL+END to go to the last used cell on your worksheet. What is its address?
NOTE: END key may be the "1" key on your Number Pad. Turn off NumLock and try it.

Alright, try this:

Please Login or Register  to view this content.

Please click the Add Reputation star below any helpful posts, and if you have your answer, mark your thread as SOLVED (Thread Tools up top). Thanks!-Lee

Hi Lee,

Hitting CRTL+END takes me to Column16379, row 17845, and the cell is empty, along with most of the range in this area of the table.

Thanks Lee, much appreciated, you have really helped me out here.