I have an issue where Remove Duplicates is not functioning as expected. I have 2 files: Source.xlsx and Calculation.xlsx
I am copying data from the Source.xlsx via VBA:
I am then removing duplicates from the copied data via VBA:Please Login or Register to view this content.
all data is being copied from Source.xlsx to Calculation.xlsxPlease Login or Register to view this content.
my findings thus far:
1. there are still duplicates in Calculation.xlsx after running VBA code
2. duplicates are all removed as intended from Source.xlsx if I run the remove duplicates VBA code
3. the duplicates exist when I manually remove duplicates from Calculation.xlsx
4. duplicates are all removed as intended from Source.xlsx if I manually remove duplicates
5. if I run the VBA code (leaving duplicates as in step 1) then sort the data in Calculation.xlsx and then manually remove duplicates, the duplicates are removed correctly.
6. if I Step by Step run the VBA macro and manually sort the imported data before the VBA Duplicate removal code runs, then allow the VBA code to run, duplicates remain.
By the Numbers:
1. Source.xlsx: 341,392 lines
2. Remove duplicates in source file (manually and via VBA) leaves 27,189 lines
3. Calculation.xlsx after data transfer: 341,392 lines
4. After VBA code runs: 27,313 lines
5. After sorting data and manually removing duplicates: 27,189 lines
6. After Step by Step running code, sorting data prior to remove duplicates VBA code running: 37,134 lines
the 27,189 figure was confirmed by sorting data and doing a line by line comparison
this was then added together, resulting in 0 duplicates.Please Login or Register to view this content.
any ideas on why the VBA isn't working in Calculations.xlsx? or why manual removal isn't working either unless the data is sorted?
Bookmarks