+ Reply to Thread
Results 1 to 11 of 11

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

  1. #1
    Registered User
    Join Date
    04-12-2011
    Location
    Bay Lake, FL
    MS-Off Ver
    Excel 2016 / 365
    Posts
    66

    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.
    Attached Files Attached Files
    Last edited by SyracuseWolvrine; 12-04-2020 at 12:42 PM. Reason: Marked as solved

  2. #2
    Banned User!
    Join Date
    02-06-2020
    Location
    Iowa City, IA, USA
    MS-Off Ver
    2016 - 365 / 2007
    Posts
    2,014

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

    delete here:
    Please Login or Register  to view this content.
    nothing wrong with that.

    sort doesn't take any time either:
    Please Login or Register  to view this content.
    why do you even have this?
    Please Login or Register  to view this content.
    that takes time.

    this:
    Please Login or Register  to view this content.
    can simply be this:
    Please Login or Register  to view this content.
    if overwriting, fine. who cares?

    furthermore, I don't see why that UCASE conversation even matters. why are you doing that? when you loop ANYTHING, it will be slow. I did not read all your code, as that's a lot. but there *are* functions to use as a substitute to looping.

  3. #3
    Registered User
    Join Date
    04-12-2011
    Location
    Bay Lake, FL
    MS-Off Ver
    Excel 2016 / 365
    Posts
    66

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

    Hi Adam, thanks for taking a look

    Quote Originally Posted by vba_php View Post
    why do you even have this?
    that takes time.
    I was using the statusbar just to get an idea where the process was in the file, because it was taking so long to process, and I wasn't sure if it was still running or had just hung.

    this:
    Please Login or Register  to view this content.
    can simply be this:
    Please Login or Register  to view this content.
    In the source data, the values would be "Not Started", "Completed", or "In Progress" ... in the output they need to be all caps, with underscores instead of spaces, since this file is going to be imported as a batch import into a SaaS platform, and it requires that format. But I could change my If statements to have the actual capitalization, rather than needing the LCase - I was trying to account for source data which may not have been uniform, but looking through the source, it actually is consistent, so I was overthinking that.

  4. #4
    Banned User!
    Join Date
    02-06-2020
    Location
    Iowa City, IA, USA
    MS-Off Ver
    2016 - 365 / 2007
    Posts
    2,014

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

    Quote Originally Posted by SyracuseWolvrine View Post
    and I wasn't sure if it was still running or had just hung.
    if any VBA routine runs for a long time, you will see a frozen screen. the environment is just not powerful. it's not supposed to be. excel is a small program. it has powerquery inside of it to substitute for a lack of coding platform and associated driver power. it's not at all like DAX for PowerBI:

    https://en.wikipedia.org/wiki/Data_analysis_expressions

  5. #5
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    19,643

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

    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.
    If posting code please use code tags, see here.

  6. #6
    Banned User!
    Join Date
    02-06-2020
    Location
    Iowa City, IA, USA
    MS-Off Ver
    2016 - 365 / 2007
    Posts
    2,014

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

    That's certainly an interesting point. interaction between memory slots versus interaction between a program object like a worksheet. Makes me think if either one is really faster than the other

  7. #7
    Registered User
    Join Date
    04-12-2011
    Location
    Bay Lake, FL
    MS-Off Ver
    Excel 2016 / 365
    Posts
    66

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

    Quote Originally Posted by Norie View Post
    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.
    Hi Norie,
    Can you have a multi-column, multi-row array? The examples I've seen of using Arrays only use a single row. (I've not worked with Arrays before - is there a reference document you've found which explains them well, including how to actually set them up?)

  8. #8
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    19,643

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

    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.

  9. #9
    Registered User
    Join Date
    04-12-2011
    Location
    Bay Lake, FL
    MS-Off Ver
    Excel 2016 / 365
    Posts
    66

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

    Quote Originally Posted by Norie View Post
    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.
    Last edited by SyracuseWolvrine; 12-02-2020 at 01:00 AM.

  10. #10
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    19,643

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

    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.

  11. #11
    Registered User
    Join Date
    04-12-2011
    Location
    Bay Lake, FL
    MS-Off Ver
    Excel 2016 / 365
    Posts
    66

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

    Thanks Norie, that helped significantly. Between that, and using arrays, I was able to get the processing time to be much faster than it was. I'm going to mark this one as solved, and post my updated code below for future people who find this thread on Google.

    Please Login or Register  to view this content.
    Please Login or Register  to view this content.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Macro optimization needed
    By Antiparras in forum Excel Programming / VBA / Macros
    Replies: 10
    Last Post: 05-03-2017, 04:15 AM
  2. Optimization of Replace macro
    By DKAbi in forum Excel Programming / VBA / Macros
    Replies: 17
    Last Post: 04-19-2013, 05:30 PM
  3. Help with Optimization Macro..
    By zealot in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 02-11-2013, 06:35 PM
  4. [SOLVED] Macro optimization for speed
    By mlegge04 in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 12-07-2012, 10:20 AM
  5. [SOLVED] Macro Optimization - Include Sum in last row of data
    By iliasark in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 09-16-2012, 03:11 PM
  6. Macro Optimization
    By intelligents in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 07-02-2008, 05:56 AM
  7. Macro Optimization
    By RH+ in forum Excel Programming / VBA / Macros
    Replies: 9
    Last Post: 04-14-2007, 06:37 PM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1