how to delete row with condition (VBA)-CodePudding

This is my excel output:

The red ones are having the same value in A and B.
The blue one is a duplication.

I want in both cases the rows to be deleted.

So that the Output looks like this:

I have tried it like this:

Sub delete()

With ThisWorkbook.Worksheets("Table10").Activate

Dim rowsend As Integer
Dim arr() As Variant
Dim element As Variant
Dim rows5 As Variant

rowsend = ActiveSheet.Cells(ActiveSheet.rows.Count, "B").End(xlUp).row
arr = Range("B1:B" & rowsend).Value
Debug.Print rowsend

rows5 = 1

For Each element In arr

    If element = Range("A" & rows5).Value Then
       Debug.Print "yes"
       rows(rows5).delete
    Else
     Debug.Print "no"
        
    End If
    
    rows5 = rows5   1
    'Debug.Print element
    
Next element

End With

End Sub

But it only deletes the following rows:

CodePudding user response：

In worder to (only) delete rows in a range, you should iterate backwards.
In order to do it efficiently (not a row at a time) you should create a Union range for the respective rows and delete them at once at the end. Iteration can be done in any way. Please, try the next code. It uses a dictionary and an array to process only in memory and creates the mentioned Union range, deleted at the end:

Sub DeleteRowsDuplic()
   Dim sh As Worksheet, lastR As Long, arr, i As Long, rngDel As Range
   
   Set sh = ActiveSheet
   lastR = sh.Range("A" & sh.rows.count).End(xlUp).row
   arr = sh.Range("A1:B" & lastR).Value2
   
   With CreateObject("scripting.dictionary")
        For i = 1 To UBound(arr)
           If arr(i, 1) = arr(i, 2) Then
                addToRange rngDel, sh.Range("A" & i)
            Else
                If Not .Exists(arr(i, 1) & arr(i, 2)) Then
                   .Add arr(i, 1) & arr(i, 2), 1
                Else
                    addToRange rngDel, sh.Range("A" & i)
                End If
            End If
        Next i
  End With
   If Not rngDel Is Nothing Then rngDel.EntireRow.Delete
End Sub

Sub addToRange(rngU As Range, rng As Range)
    If rngU Is Nothing Then
        Set rngU = rng
    Else
        Set rngU = Union(rngU, rng)
    End If
End Sub

If the range to be deleted becomes huge, creation the the Union range slows down and the code is not so fast as for reasonable ranges. If this is your case, please state it and I will post another code able to also deal with such a situation...

CodePudding user response：

How to delete entries from a collection? This is such a frequent problem :-)

Let me give you an example of how it can go wrong, by delete all the even values from the collection (1, 2, 4, 6, 7, 8), using the following pseudo-code:

int index = 1; // we'll start at 1

while (index < length(collection))
do:
     if collection[index] mod 2 = 0
     then collection.remove(index);
     index = index   1;
end while

Watch what happens:

index   collection         action and result
1       (1, 2, 4, 6, 7, 8) none
2       (1, 2, 4, 6, 7, 8) remove second => (1, 4, 6, 7, 8)
3       (1, 4, 6, 7, 8)    remove third  => (1, 4, 7, 8)
4       (1, 4, 7, 8)       remove fourth => (1, 4, 7)

What has value 4 not been removed? Well, simply because, due to the removal of the second item, value 4 became the second item, and the loop went through to the third item, skipping value 4.

How can we solve this? Simply by going from the end back to the beginning:

int index = length(collection); // we'll start at the end

while (index > 1)
do:
     if collection[index] mod 2 = 0
     then collection.remove(index);
     index = index - 1;
end while

index   collection         action and result
6       (1, 2, 4, 6, 7, 8) remove sixth => (1, 2, 4, 6, 7)
5       (1, 2, 4, 6, 7)    nothing
4       (1, 2, 4, 6, 7)    remove fourth => (1, 2, 4, 7)
3       (1, 2, 4, 7)       remove third => (1, 2, 7)
2       (1, 2, 7)          remove third  => (1, 7)
1       (1, 7)             nothing