Page 5 of 5 FirstFirst ... 345
Results 41 to 47 of 47

Thread: Should this code be written differently? Takes way longer than sorting each sheet manually

  1. #41
    Board Regular
    Join Date
    May 2019
    Posts
    54
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Should this code be written differently? Takes way longer than sorting each sheet manually

    Sorry....maybe I was misunderstood.

    only a zero distance would show up in the results if there were any duplicates in the AB column
    Last edited by strat919; Jul 27th, 2019 at 07:33 AM.

  2. #42
    Board Regular
    Join Date
    May 2019
    Posts
    54
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Should this code be written differently? Takes way longer than sorting each sheet manually

    A B
    1 42.4822 20.7458
    2 42.2139 20.7397
    3 42.9144 20.6897
    4 42.5781 21.5803
    5 42.3214 21.3583
    6 42.9144 20.6897
    Sheet1



    A B C D E
    1 42.9144 20.6897 42.9144 20.6897 0
    2 42.2139 20.7397 42.4822 20.7458 18.54432
    3 42.3214 21.3583 42.5781 21.5803 21.04423
    4 42.9144 20.6897 42.4822 20.7458 30.00411
    5 42.3214 21.3583 42.2139 20.7397 32.49618
    6 42.3214 21.3583 42.4822 20.7458 33.1735
    7 42.5781 21.5803 42.4822 20.7458 43.01226
    8 42.9144 20.6897 42.2139 20.7397 48.47707
    9 42.5781 21.5803 42.2139 20.7397 49.73902
    10 42.9144 20.6897 42.5781 21.5803 50.8234
    11 42.9144 20.6897 42.3214 21.3583 53.24988
    Results 190727 7.38 am

    Row 3 and 6 are duplicates so it showed up one time as 0 distance in results
    Last edited by strat919; Jul 27th, 2019 at 07:44 AM.

  3. #43
    Board Regular
    Join Date
    Mar 2015
    Posts
    3,875
    Post Thanks / Like
    Mentioned
    72 Post(s)
    Tagged
    7 Thread(s)

    Default Re: Should this code be written differently? Takes way longer than sorting each sheet manually


    How can there be duplicates in AB ?
    - the code should remove duplicates in this line
    Code:
    'remove duplicate co-ordinates and place remaining values in array
        Data.Range("A:B").RemoveDuplicates Columns:=Array(1, 2)
    Last edited by Yongle; Jul 27th, 2019 at 08:55 AM.

  4. #44
    Board Regular
    Join Date
    May 2019
    Posts
    54
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Should this code be written differently? Takes way longer than sorting each sheet manually

    The duplicates should not be "removed" in the code. There will be very few cases where there will exact duplicates...... but I would need to see if there were any..... and they would appear at the top of the results as the the shortest distance being zero distance.

    I guess everything would work perfectly if the code simply didn't remove any exact duplicates.

    I hope this makes sense to you
    Last edited by strat919; Jul 27th, 2019 at 10:13 AM.

  5. #45
    Board Regular
    Join Date
    Mar 2015
    Posts
    3,875
    Post Thanks / Like
    Mentioned
    72 Post(s)
    Tagged
    7 Thread(s)

    Default Re: Should this code be written differently? Takes way longer than sorting each sheet manually

    Why not simply delete the line that removes duplicates ?

  6. #46
    Board Regular
    Join Date
    May 2019
    Posts
    54
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Should this code be written differently? Takes way longer than sorting each sheet manually

    I tried that, but got an error.... I will try it different ways

  7. #47
    Board Regular
    Join Date
    May 2019
    Posts
    54
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Should this code be written differently? Takes way longer than sorting each sheet manually

    I didn't delete the entire line.

    It works perfectly now Thank you so much for your help Mr Yongle!

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •