Macro Optimization - Loop through large data set, copy/reformat some data to separate WS

Hi everyone. I think this is a bit outside the normal realm of this forum, but I'm hoping you can help. I've got a macro that reads through one sheet, and puts some data in another sheet, based on a few criteria. The macro is working, but it's very slow - it took about 7 minutes to get through 5,000 lines, and my full source data is about 300,000 lines. Ideally, I'd love to make this process much faster.

I've attached a sample file with the macros included. The "Process File" will do the following:

• Clear all data on the "EIB" sheet
• Sort the data on the "ImportedData" sheet
• Remove the HTML tags from Columns B and C of the "ImportedData" sheet
• If there's no data in Column B, use the first 80 characters from Column C
• Reformat the dates from "YYYY-MM-DD HH:MM:SS UTC" to "YYYY-MM-DD" if it's a valid date (there are some data entry errors in the original source, for example a 3 or 5 digit year)
• If the "Status" column is NOT "Inactive", or the status is "Completed" with a date after 10/1/2020, copy some of the columns to the "EIB" sheet

I also manually processed the first 5 rows of the "ImportedData" sheet onto a new sheet called "Manual - 5 rows", so you can see what the output should look like.

Please Login or Register  to view this content.

You should read the data into an array, process that array and then write it to the other sheet.

That avoids multiple interactions with the original sheet and is likely to speed things up significantly.

Yes you can have a mult-column, multi-row array.

In your case you could read all the data into an array with simple code like this.

Please Login or Register  to view this content.

Originally Posted by Norie

Yes you can have a mult-column, multi-row array.

In your case you could read all the data into an array with simple code like this.

Hi Norie, thanks for the tip on declaring the array.

I tried it, and found that everything was still pretty slow to process ... only did 5000 rows as a test, and it still took nearly 7 minutes. So, I started commenting out one column at a time, to see if that improved things, then adding it back in. I discovered that the problem seems to be my "HtmlToText" function, which I'm using to remove HTML tags in 2 columns. When I commented out the lines that call that function, processing time was VASTLY improved.

I think the problem is that I'm calling the function for each cell that I need to process - 2 columns, times 5000 rows, equals a lot of calls to this function. I do like using the "innertext" method of removing HTML, as it seems to give a little cleaner result than just doing a replace <*>, but this is a function I found online, and I can't seem to modify it to clean multiple cells at once.

Please Login or Register  to view this content.

Every time you call the HtmlToText function you are creating a new HTML document, I would think that's bound to take some time.

I don't know if it'll speed things up but why not create the HTML document at the start of the code in the main module and re-use the same document throughout.