Optimize VBA with multiple loops

  • Hello - I am trying to optimize this code so that I can easily call out this section of code multiple times. I cannot make it a sub procedure because it contains

    If ActiveCell.End(xlDown).Row = 1048576 Then Exit For

    'Dim this as something? or Set it? Make it a function?
    counter1 = 1
    Do Until counter1 > 9
    If ActiveCell.End(xlDown).Row = 1048576 Then Exit For
    counter1 = counter1 + 1

    Here's the current full code:


  • Re: Optimize VBA with multiple loops

    Can you upload a sample workbook? Or at least try to explain what you are trying to do with this code? It is likely that there are many ways this code could be improved if we understood your goals better.

    Regardless, I do have a simple suggestion that would allow you to move your loop code into a sub:

    -Declare a Boolean at the module level
    -Change your "If, Then, Exit For" code to "If, Then, modify boolean, Exit Sub"
    -Check the boolean value to exit the "For Each" loop each time you run the sub

    So it could look something like this:

    I hope this gives you some ideas.


Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!