Need Help Making Macro Faster/More Efficient

[rbac]

The below macro is incredibly slow, on my rather slow computer I calculated it will take about 21 hours to run the macro, which is just ridiculous, I could probably do it manually in the same time or faster.

Is there any obvious way anyone sees to make this code faster?

Also, the commented out part about deleting the rows seems to run into some sort of error I can't figure out....

Thanks!

Please Login or Register  to view this content.

[stnkynts]

Of course it is going to take forever. You have a loop within a loop doing 19000 loops, 648 times. So that is 12 million times you are running your code. Did you expect it to be instantaneous? Not only that but you didn't declare any variables so everything is running as a variant.

A possible solution (not sure because we get no other information than your code. This is untested)

Please Login or Register  to view this content.

[rbac]

I know it is going to take forever, and on top of that I still need to run the loop thats commented out... this is like the third time I've ever used VB or made a macro, and I havn't done any programming in years (and that was just basic classes in HS) its why i asked for help here....

[stnkynts]

Did the above amended macro work for your situation? If you want further assistance you will probably need to upload a copy of your workbook, with the sensitive material removed, so that we can get a better feel for what is going on.

[rbac]

Did the above amended macro work for your situation? If you want further assistance you will probably need to upload a copy of your workbook, with the sensitive material removed, so that we can get a better feel for what is going on.

It would be faster to make a dummy spreadsheet than remove the sensitive data which is virtually all of it.

I have not had a chance to try it, but the bottom part that is commented out I solved myself, I was just missing an "End If" statement. But it seems to delete only a few rows that it should not all of them... ill post more info later when i have a chance to look at it some more thanks.

[judgeh59]

On the delete row area....you may want to switch the startrow and endrow...when you are deleting rows like this the FOR loop counter gets confused and actually skips over rows because you have deleted a row and the FOR loop counter get incremented...so if you work backwards it should work...the other way is to keep the BeginRow and EndRow the same but inside the IF statement and after the actual Delete decrement DeleteRow....HTH

Please Login or Register  to view this content.

[rbac]

Thanks thats all I needed to make this work properly, still slow, but I found a faster computer to run it on and it only takes 20 minutes to run on that computer.

On the delete row area....you may want to switch the startrow and endrow...when you are deleting rows like this the FOR loop counter gets confused and actually skips over rows because you have deleted a row and the FOR loop counter get incremented...so if you work backwards it should work...the other way is to keep the BeginRow and EndRow the same but inside the IF statement and after the actual Delete decrement DeleteRow....HTH

Please Login or Register  to view this content.