0

I've used a macro on MSExcel for some months now, but, for some reason, it has stopped working.

Its supposed to check if the index already exists in one table, filter it and list the "new entries", but it has started outputting duplicates. I've checked the database it's supposed to use as parameter, but didn't find any inconsistence in my index column, so I might need help with my coding.

I have 3 sheets, one called "1- colar nova base" where I input my data, and the other one where my database is, is "sheet3", and the "3- novos" is my output sheet

Here's the coding:

Sub NovosCasos()

    Application.ScreenUpdating = False
    Application.EnableEvents = False
    Application.DisplayAlerts = False
    
    Dim NIF As String
    Dim ActiveRow As Integer
    
    'Filter and copy the new data
    
    Sheets("1- Colar Base Nova").Select
    Range("A1").AutoFilter Field:=8, Criteria1:="DOC. EXPIRED"
    Rows("2:2").Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Delete Shift:=xlUp
    Range("A1").Select
    ActiveSheet.ShowAllData
    
    Rows("2:2").Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Copy
    
    Sheets("3 - Novos").Select
    Range("A2").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
    
    'Verifying new index value (NIF) and exclude existing ones
    
    ActiveRow = 2
    
    
    Limit = Range("c" & Rows.Count).End(xlUp).Row
    
    
    For i = 2 To Limit
        
        Do Until sheet2.Cells(ActiveRow, 3) = ""
        
            If sheet2.Cells(ActiveRow, 3) = Sheet3.Cells(i, 3) Then
                sheet2.Cells(ActiveRow, 3).EntireRow.Delete
            End If
        
            ActiveRow = ActiveRow + 1
        
        Loop
        
        ActiveRow = 2
        
    Next i
    
    Application.ScreenUpdating = True
    Application.EnableEvents = True
    Application.DisplayAlerts = True


End Sub

Any ideia why it's started outputting duplicate entries? (entries that aren't new, they already are in the database)

roalfa
  • 1
  • Site note: recommended reading is [how to avoid using Select](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba). – BigBen Jun 16 '23 at 13:13
  • I think you should take a look at this: https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba – z32a7ul Jun 16 '23 at 13:13
  • Normally if deleting, you want to loop from the bottom upwards, or even better, use `Union` to create a range to delete and then delete *after* you've finished looping. – BigBen Jun 16 '23 at 13:14

0 Answers0