Optimize Excel Code - Too many FOR NEXT Loops

Hi All, Attached is a macro-enabled workbook with a lot of For Next Loops. Run speed is mostly bearable except for the Summarize button which calls a Summarize Sub. Its actually 3 Subs strung together with Two Calls between them -Summarize, Summarize2, Summarize3. The gist of this application is that the user runs through the buttons on the Main worksheet in order from left to right. It all starts with Importing a Before and After html file, then comparing them for differences. I would supply the two files, but we can't upload that file type apparently.

Anyway, I'm a novice with VBA. Love it and I certainly see its huge potential, but I mostly have to steal code (or beg help) and modify to fit my situation. Could someone with more experience give me any tips to speed up the code? Again, its mainly the Summarize Subs with SOOOOO many For Next loops. Is there a better way? Password for the vba module is dmu123xls.

Thank you, tdsg

Originally Posted by tdsg

I would supply the two files, but we can't upload that file type apparently.

Hi,

as it's not difficult to add a valid extension at the end of the filename like .txt …

I zipped the two files. Good idea on using a different file type!

All the code works, just too slow especially when you click on the Summarize button. The other buttons run in less than about 15 seconds each, but the Summarize one takes 2-3 minutes and is what populates the Summary worksheet.

Yep, i was afraid that would be missed in the last sentence of my post. Should have highlighted it, The password is dmu123xls.

tdsg

I know, apologies. That part was done by Forum Guru, jindon and is way beyond my capabilities. I could barely read it to tweak it. He figured out how to get the rows to align when records (rows) were either added or deleted between the before and after parts. Otherwise, comparing would be easy since all the columns would line up on the same rows if edits to existing data (rows) was all that was done. Hope this makes sense. I'm not good at adding comments either for all the For Next loops. The Compare worksheet shows all the rows and turns red anything that changed. The Summary worksheet is where i'm trying to show only the rows that turned red where something changed (delete row, add row, or change data in existing row).

I'm sorry. Not the Compare worksheet. Meant to say the Main worksheet shows all the rows and turns red anything that changed.

Your loops are hopeless...
e.g.

Please Login or Register  to view this content.

You are looping entire column...
Find method.
eg

Please Login or Register  to view this content.

You also need to review "Sub Summarize_click()"

Amazing code, jindon. Thank you so much. Incorporated with slight tweaks to formatting (some color changes0. Is there a way to also turn red the font in columns AM to BW? You know, the ones that are counterparts to the ones turned red in columns A to AK. Its nice to be able to scroll right and quickly locate what the values were before edits.

Presently, all the Summarize button does is to copy the Main worksheet over to the Summary worksheet. What I need it to do is only show rows that have something red in it and the column header for that. Earlier I successfully did this with an insane amount of For Next loops. I have since deleted that because it is too slow and this was the reason for this post. Do you know how to accomplish this for rows 3 to lastrow on the Summary worksheet? Since there are so many rows on the Main worksheet, its a lot to scroll through to find the red ones that changed. That is why I want to show only the rows with some red and their headers on the Summary worksheet. Hope this makes sense. Latest workbook attached. Module password is the same - dmu123xls.

tdsg

Shutdown my pc already, so I will have a look at it sometime tomorrow.

You're awesome, jindon. I'm sure you recognized the Align Sub code that you helped me with in another post. Thank you for taking an interest in this application and helping me so much. Believe it or not, I am learning a lot with help from people like you on this forum. I do struggle to read or modify your code though - too advanced for me, ha. Any further help you can provide at your convenience will be a blessing to me. Cheers!

Too many lines delete/add/changed etc...
Try the attached ans see how it goes.

I disabled ProtectAll for testing porpose.

Hey jindon, thank you! I got an error on this line of code for the Compare Sub.

Please Login or Register  to view this content.

I could not try out the Summarize code. Do you know the issue with the above? I cleared all then imported both files, then clicked the Compare button and got the error.

Oops, typo
should read as Nothing.

Please Login or Register  to view this content.

I am so pleased with your help, jindon. Both worksheets, Main and Summary, do what I want them to do because of your talents. Users can see everything, changes or not, on the Main worksheet. They can also see a lot fewer rows (less scrolling) on the Summary worksheet with changes only. Perfect! I'll try testing this out with a larger import and see what the wait time is for each button. I fear that some of these datasets could be quite large and the code will bog down. We'll see though. It might be okay. I'll mark this as solved since my code has been optimized, or is at least soooo much faster than when I had all those For Next loops.

Attaching the final code and the before and after text files in case this is useful to anyone else with similar needs that could modify.

module password is still dmu123xls

Cheers!

Used CF to avoid slow loop

Please Login or Register  to view this content.

Any speed enhancements will be huge. The sample before and after files are tiny compared to what they will be. Unfortunately, the code above caused me to loose the gray header interior colors on all columns except A and AM.

OK change to

Please Login or Register  to view this content.

Hi jindon, That definitely helped. It added back in the gray headers for columns A through AK. I added a WITH after yours for columns AM3 to BW and lastrow (bold below) to get the gray headers for those, but for some reason, the fonts are all turning red in those columns. I'm just not skilled enough to edit your code with any degree of confidence that i did not mess it up.

Please Login or Register  to view this content.

Hope this one is ok

Please Login or Register  to view this content.

Much better as far as applying the correct formatting to the header rows versus data rows (gray/white). But, for some reason the red cells are not matching up between columns A-AK and AM:BW when I scroll right to see what the value was before changes.

I figured it out. Changed this

Please Login or Register  to view this content.

To this

Please Login or Register  to view this content.

See.. I'm learning enough to help myself a little. Thanks again, jindon, for all the help.

Attached is latest. dmu123xls password still. Works great and does exactly what I want with a small dataset (it has loaded now just 3 cycles of the data)

I tried it with 500 cycles and could import both a before and after file (same file, I was just testing how long this would take) in about a minute each button.

Unfortunately the Compare button bombed and it just spun for at least 20 minutes before I ended it with task manager.

jindon, Is there anyway to optimize the part below that aligns the before and after rows? This was an essential part you helped me with on another post and works great with a small dataset. It may already be as efficient as possible and I'll need to lower my expectations as far as what is possible with vba.

Please Login or Register  to view this content.

Alright, thank you. I tried 100 cycles of data which amounted to 57,000 rows and the Compare button ran in about 90 seconds. I wish it would handle more, but, if its the formatting code that slows it down, i don't know of a way around that.

I imported html files initially, but switched to text files later thinking that was faster. I'm wondering if I should switch back to html files that include formatting so that the only formatting I need to do with vba is to turn them red when different. Maybe I can pursue this and see where that goes.