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

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


    Code
    '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
ActiveCell.End(xlDown).Offset(z).ClearContents
ActiveCell.End(xlDown).Offset(z).Select
counter1 = counter1 + 1
Loop



    Here's the current full code:


    Thanks!

    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.


    Cheers

Participate now!

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

Register Yourself Login