VBA IF AND for inserting/deleting rows

[airelibre]

Hi, I have two sheets with a list of data on each, both have ID codes for each row, but they don't always match:

Sheet 1 should have all the codes but it also duplicates some, and the duplicates must be retained:
Eg
1
2
2
2
3
3
4
5
6
7
7
8
9
[...]

Sheet 2 doesn't have all the codes:

1
2
3
5
7
9
[...]



I've ordered them both from smallest to largest, and I want to write a macro that will check if they match. If they don't, I want to first check if it's because of a duplicate on sheet 1, in which case I will insert a blank row in sheet 2, or if it's not because of a duplicate, I want the row in sheet 1 deleted. This is the code I came up with, which doesn't work. It doesn't insert rows, and if I keep using it it eventually deletes all but the first 30 or so rows:


Code:
Sub ConsolidateRows()
Dim i As Long
For i = 2 To 100 Step 1
If (Worksheets("Sheet1").Range("D" & i).Value = Not Worksheets("Sheet2").Range("A" & i).Value And Worksheets("Sheet1").Range("D" & i).Value = Range("D" & i - 1).Value) Then
Worksheets("Sheet2").Range("A" & i).EntireRow.Insert
End If
If Not Worksheets("Sheet1").Range("D" & i).Value = Range("D" & i - 1).Value And Not Worksheets("Sheet1").Range("D" & i).Value = Worksheets("Sheet2").Range("A" & i).Value Then
Worksheets("Sheet1").Rows(i).EntireRow.Delete
End If
Next i
End Sub
Where am I going wrong?

To clarify, taking my above examples, my end result should look like this:

Sheet 1:
1
2
2
2
3
3
5
7
7
9

Sheet 2:
1
2


3

5
7

9

 

[Fluff]

Hia & welcome to MrExcel
This works on your example, but is not very pretty.
Unfortunately I can't think of a better way of doing it.

Sub ConsolidateRows()
'airelibre
    Dim i As Long
    Dim Sht1 As Worksheet
    Dim Sht2 As Worksheet
    
    Set Sht1 = Sheets("Sheet1")
    Set Sht2 = Sheets("sheet2")
    
    For i = 2 To 100 Step 1
        If Not Sht1.Range("D" & i).Value = Sht2.Range("A" & i).Value Then
            If Sht1.Range("D" & i).Value = Sht1.Range("D" & i - 1).Value Then
                Sht2.Rows(i).Insert
            ElseIf Not Sht1.Range("D" & i).Value = Sht1.Range("D" & i - 1).Value Then
                Sht1.Range("D" & i).EntireRow.Delete
                i = i - 1
            End If
        End If
    Next i
End Sub

 

[MrTeeny]

Whenever you're deleting or inserting rows it's always best to work upwards rather than down as inserting/deleting cells messes with your for - next count

Hven't looked thru your code but maybe try amending the loop to

For i = 2 To 100 Step 1

to

For i = 100 To 2 Step -1

 

[Fluff]

@MrTeeny
Unfortunately I don't think that will work as the 2 ranges being compared are of different sizes.