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

1. ## 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?  Reply With Quote

2. ## Re: How to speed up a macro handling a large amount of data? Originally Posted by jacksongf 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  Reply With Quote

## User Tag List

data, fast, formulas, optimizing vba code, paste as values 