Looking to make this macro Faster

  • Hi,

    Loooking to make this macro faster


  • Where you have,

    1. desWS.Cells(rngList(Val), 9) = v2(i, 2)

    Is it possible to firstly read all the contents from column 9 in desWS worksheet into a variable, and then write into this new variable and finally write back out to the desWS worksheet at the end after the second loop? Something like this:

    I made an assumption that the number of rows for column 9 in desWS is the same as column B.

  • The scripting dictionary will not allow you to add a duplicate key. As such, it must check to make sure the key already exists when you add it. Your code forces it to check twice. The way to avoid this is to turn off error trapping for just long enough to add the keys. Duplicates will still not be added.

    1. On Error Resume Next
    2. For i = 1 To UBound(v1, 1)
    3. rngList.Add Key:=Val, Item:=i + 1
    4. Next i
    5. On Error Goto 0 'Turns error catch back on

    This should speed your code up by a fair amount if you are processing long lists.

    I am not sure if this is your intention. As you have not uploaded a sample, I don't know what you are processing.

    But, you are saving the value from the array as the key and making the item value of the dictionary the index of the array (+1?!?!) (which will not be sequential if there are duplicates!) This may be by design, but I don't see it.

    Consider making the key and and the item the same for purely esthetic purposes. Is there a reason to save the value of i?

  • TonyTuperello

    Welcome to the Forum. Please read the Forum Rules to understand how the Forum works and why I have added Code Tags to your post

    All VBA code posted in the forum must be wrapped in code tags, which you omitted, including single-line code snippets.Be sure to use them in future posts.

    How to use code tags

    Note: no apostrophe in the tags, just used for demonstration here.


    your code goes between these tags


    Or, just highlight all of the code and press the <> in the post menu above button to add the code tags.