Bulk Merge Text through Arrays to reduce processing time

Hello Seniors,

Attached is the Sample Workbook, starting data from ROW 7 till 26407, - truncated due to 1 MB file size restrictions. Practically there are much higher ROWS 225897 to over a half Million ) in Worksheets - Sheet1

Data starts from Column B through P
Column C having Text Data
Column D having DATE data
Column E having Numeric data
Column F having Text Data
Column P having DATE data

Column A is where I merge text like

Please Login or Register  to view this content.

Following is the macro code to fill/populate rows in column A

Please Login or Register  to view this content.

This takes about 4 to 8 seconds presently depending on ROWS data.

Seek Guidance for two aspects

1) Can I reduce the processing time further through transferring data to Arrays and Looping and move/paste data to rows in Column A, as is done presently but through formula filling.

2) I run Timer to measure the time elapsed, but some how the outcome comes in twelve hour format, such that for 5 seconds spent, i get message - 12:00:05 AM. How Can i display this result in simple 00:00:00 format i.e. HH:MM:SS without AM PM ?

Thanks

See if this runs faster

Please Login or Register  to view this content.

Originally Posted by jindon

See if this runs faster

Please Login or Register  to view this content.

It was a Bomb, took just 0.73 seconds on ROWS 272892+ compared to over 6 seconds earlier. Thanks a lot. I just added towards end of macro a line to deactivate range copy

Please Login or Register  to view this content.

Originally Posted by alansidman

Using Power Query on your example it is almost instantaneously

Please Login or Register  to view this content.

Power Query is a free AddIn for Excel 2010 and 2013, and is built-in functionality from Excel 2016 onwards (where it is referred to as "Get & Transform Data").

It is a powerful yet simple way of getting, changing and using data from a broad variety of sources, creating steps which may be easily repeated and refreshed. I strongly recommend learning how to use Power Query - it's among the most powerful functionalities of Excel.

- Follow this link to learn how to install Power Query in Excel 2010 / 2013.

- Follow this link for an introduction to Power Query functionality.

- Follow this link for a video which demonstrates how to use Power Query code provided.

Thanks, I will certainly explore it[Power BI]. I thought, due to my old version of Excel, it is not useable and ignored for years.

Originally Posted by CheeseSandwich

Another option, Evaluate:

Please Login or Register  to view this content.

Certainly, I will check.

Regards

Originally Posted by CheeseSandwich

Another option, Evaluate:

Please Login or Register  to view this content.

good enough, 1.37 seconds for rows about 223300+