Page 2 of 2 FirstFirst 12
Results 11 to 12 of 12

Thread: How to speed up a macro handling a large amount of data?
Thanks Thanks: 0 Likes Likes: 0

  1. #11
    New Member
    Join Date
    Mar 2017
    Location
    Winnipeg, Manitoba, Canada
    Posts
    21
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: How to speed up a macro handling a large amount of data?

    Hi there, can you explain to me why this would be faster than simply having a static range like $C$2:$L$250000? Wouldn't a dynamic range only help with determining the exact amount of rows there are, so it would only save a little bit of time? Eg. dynamic may find 237,000 rows while static would have 250,000, so it would only save 13,000 rows worth of where its looking?

  2. #12
    Board Regular
    Join Date
    Sep 2013
    Location
    Blue Mountains, Australia
    Posts
    3,485
    Post Thanks / Like
    Mentioned
    5 Post(s)
    Tagged
    0 Thread(s)

    Default Re: How to speed up a macro handling a large amount of data?

    Quote Originally Posted by jacksongf View Post
    Hi there, can you explain to me why this would be faster than simply having a static range like $C$2:$L$250000? Wouldn't a dynamic range only help with determining the exact amount of rows there are, so it would only save a little bit of time? Eg. dynamic may find 237,000 rows while static would have 250,000, so it would only save 13,000 rows worth of where its looking?
    Yes, if that were the case, you're not going to save a huge amount of processing time.

    But you should be able to save heaps of time by sorting your VLookup table (as Mark858 mentioned). You're looking for an exact match, which means the search is linear and Excel might need to go through the entire table looking for a match (or not).

    If you sort the table, you can use a much faster binary search method. Here's one way you could do this:

    B10: =IFERROR(IF(VLOOKUP(A10,A$2:A$7,TRUE)=A10,VLOOKUP(A10,A$2:B$7,2,TRUE),"Can't find"),"Can't find")

    Excel 2010
    A B
    1 Sorted
    2 A xyz
    3 B abc
    4 C ghi
    5 E qwe
    6 G asd
    7 H zxc
    8
    9 Lookup
    10 C ghi
    11 D Can't find
    1

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
  •