0

I am struggling with writing a vba code for getting the deleted rows data and added rows data in latest excel file while comparing with old excel file.

example: latest workbook(WB) have 405 rows of data and old workbook(WB) have 400 rows of data. the factor is - might have added 5 rows in latest WB or might have deleted 2 rows and added 7 rows in latest WB . if it is added (row was added in latest WB), have to get the added rows data from latest WB, if it is deleted(the rows was deleted in latest WB), have to get the deleted rows data from old WB . Please help me.

Community
  • 1
  • 1
Ashok
  • 284
  • 2
  • 5
  • 23
  • What have you done so far? Where are you stuck? – Maciej Lipinski Jul 05 '17 at 10:32
  • @MaciejLipinski i googled but couldn't found the way to which formula should use in the coding. still now i am in the first line – Ashok Jul 05 '17 at 10:58
  • You could try to do it with nested loops. For each row in the old workbook, check if there's an identical row in the new workbook. If there isn't, it means it was deleted. Then do the same again but have the outer loop go over the new workbook and the inner go over the outer loop. The unmatched rows are the added rows. It's not very efficient but for 400 rows it'll do. – Maciej Lipinski Jul 05 '17 at 11:09
  • 1
    @MaciejLipinski You might want to load the records into some array before taking that approach, unless you have a few minutes to spare each time you make the comparison. You also might want to add a unique key helper column and check on that one instead of all fields in all rows. If it's 400 rows, sure, but when comparing 10K+ rows, nested loops come straight out of hell. – Rik Sportel Jul 05 '17 at 11:30
  • @RikSportel agreed, I suggested this approach because it's not very complicated to implement and it's a starting point for OP. Once it's operational you could optimize it as you said. OP didn't say what was the basis for row comparison so perhaps checking all fields is not necessary. – Maciej Lipinski Jul 05 '17 at 11:45
  • Possible duplicate of [Excel vba, compare rows of two workbooks and replace](https://stackoverflow.com/questions/11753724/excel-vba-compare-rows-of-two-workbooks-and-replace) – J. Chomel Jul 06 '17 at 12:51

0 Answers0