I need to create a VBA script that will scan the current xls sheet (XLSA) (one column in up to for sheets) and compare it to a second xls (XLSB) to check if the ID has bee used before. Easy enough, except that the xls being compared to has 443K rows.

Just wanting advice to best tackle this. Should I read the entire 440K rows (a single column) into an array then scan the array for the value in XLSA on each row? or use Find? it needs to be very accurate in that we cannot have a duplicate ID.

Thanks!