How to make this code work Fast - while handling more data

Hi all,

i have this code to list all the file in the folder and to identify new added one.
This code works really great when the folder is have files around 200. But this code really get too slow as one of the folder which i mapped got 25000 files.

could anyone enhance this code to handle this quickly.

note: i have already tried screen updating false, calculation manual. But still code take around 15 mins to execute and file turn in to not responding. After waiting for quite long time. File lists all the 25000 files.

help would be much appreciated.

Attached sample file for your reference.

Maybe it's the deletions try this block deletion:

Please Login or Register  to view this content.

Ok so I moved the dim statements that belong inside the module, into it...
I changed your new files listing method, re-using a little bit of your code, but:
It does not copy all the listed files into new and then compare to clear, instead I filled arrays with the old list and the current list, and added the old files to a dictionary.
Then we use the dictionary to see if the current files array items are from the old files list, and only if No, do we place it in the new files list.
See if this works for you:

Please Login or Register  to view this content.

@xladept, I guess we arrived at the same conclusion for the root of the issue, even if we approached the solution differently

This listed 667 files in less than a second:

Please Login or Register  to view this content.

@ Arkadi - looks more like you're between shg and me

@xladept indeed! I just tackled the main issue while shg was more thorough and wrote a whole new sub that looks to be much more efficient.

@shg nicely done sir!

@ Arkadi - Yeah- and I just put a patch in the existing code

Hi all,

i would like to thank all for your contribution.

would like to understand code shared by "Shg". i tested the codes, i can see the list of files being published in the first sheet.
But under header old files it is listing all 10 file names in my folder. And the same list of 10 files being listed under new files header.

can you please explain for better understanding.

When you already have previously listed data on the sheet, just delete column A ("old") and run again. That treats the new column A (which was col B, "current") as "old"