Problematic VBA Code for Formatting

Hello all!

Over 2 years ago, I developed a macro for my firm in order to format raw data output from our inventory database into a formatted and polished file we could send externally to clients. It always ran a bit slow (around 5 minutes), but other than that we had no issues. We recently changed from Office 2016 to Office 365, and around the same time people started noticing spikes in the macro run time of over 10 minutes. No changes had been made to the code itself, but all of a sudden the runtime became astronomical. I went back in to the file and cleaned up the macro in an attempt to improve speeds. It seemed to work well, with run times for smaller files down around 1 minute, but larger files are still taking 10 minutes or more. I believe the bulk of the time is spent performing the last function of the macro which is an If, Then function. When re-writing I tried to limit the number of cells being checked using a LastRow function, but I'm not sure I got it quite right. I'm going to post the code below. Maybe someone will see something that I'm not. I'm not an expert with VBA, so any input or feedback would be extremely helpful!

Please Login or Register  to view this content.

Not sure how much it slows things down, but all of those 'single line' with statements can be combined into a single statement. For example:

Please Login or Register  to view this content.

can become

Please Login or Register  to view this content.

I think you have a bug here:

Please Login or Register  to view this content.

If Lastrow is 5 then the string above becomes "A2:J25" and I think you want "A2:J5". Just change the J2 to just J. But this should not be a big time killer since it is just formatting.

Need to look for something that really adds time as it gets bigger...
Ah - same type of bug here in your For loop:

Please Login or Register  to view this content.

A couple of things here.
1) Similar bug as before. If Lastrow is 5, then you are doing the range D4:J25. And as Lastrow becomes bigger that just goes up orders of magnitude (e.g. Lastrow=100, you are now going through D4:J4100).
2) That loop goes by cells - your c is a cell. Yet, the operations are row based. So, the first loop uses D4 and adjusts row 4. The second loop is E4, which also adjusts row 4. The third loop is F4, which also adjusts row 4. You are adjusting the same row 7 times when you only need to do it once.
To fix both those issues:

Please Login or Register  to view this content.