If your worksheet contains contains unique value (or set of values) which identifies record, you need to use Database Comparison option.
Database Comparison option completely changes algorithm used to compare 2 worksheets for differences.
Data is sorted by Primary Keys and compared only by key values. Records with ideintical keys are compared for changes in all columns.
With this option enabled, xlCompare also detects Added and Deleted rows. If your worksheets contain different set of fields - you'll get list of added and deleted fields.
Database Comparison option enables other features, applicable for database tables:
- Highlight Primary Keys
- Freeze Primary Keys