Hi Everyone:

I have a list of Names (From A2: A2500). This list is created from a
Database Query from MsAccess. Whenever I refresh (Update) this query,
what I need is, to get the new entries in the list. I dont want to
copy the current list into another range and compare it with list I am
getting after refresh. How can I keep the current list in the memory
and compare that with the new data (after refreshing the query)? Any
help will be highly appreciated. Thanks


Regards


Mac

Hi Mac,

can you store the list in a collection?

Regards,
Ivan

Yes, My problem is, i am not that familiar with Array. (What I used
to do is, Copy the list to another range and check for new entries with
Application.worksheetfunction.Countif).

Can you give me some code for how to store it in a collection and how
to check it with current list?

Hi Mac,

collection attitude is applicable only if your list of names before and
after refreshing consists of unique records. Is that true?

Regards,
Ivan

Yes, it is unique records. There will not be any duplicate records in
the list.

Hi Mac,

I don't have any suiteble code example I could send to you, so I will
give you hints and if you are able to write the code yourself,
everything should be fine.

You add all the names into a collection. While adding into a collection
you get an error everytime you are trying to add something with a key
that is already used. So if you after refresh try to add any of the
names, that were previously used as a key, you get an error, only new
keys (new names after refreshing) will not invoke an error. You can
trap these errors in your code, so you will know which names are new.
These new names that will not invoke error will be added to the
collection, so your collection will contain all names after refresh and
will be prepared for next comparison.

Please let me know if something is not clear.

Regards,
Ivan

Thanks Ivan.

Done.


Regards


Mac