How to delete duplicate row with condition in VBA

Question

Im completely novice on VBA but i found no solution in google so far. i find 2 seperate code but when combining the solution didnt find out

I have a issue of deleting duplicate row with condition

column b    column z 
  1         22/1/2019 
  2         22/1/2019 
  1         23/1/201  
  2         23/1/2019

outcome

column b    column z 
  1         23/1/2019 
  2         23/1/2019

My code ( having problem when nested do while with for )

  Sub Macro1()
Dim RowsToDelete As Range, innerRow As Long
Set TheRng = Intersect(ActiveSheet.UsedRange, ActiveSheet.Range("B:Z"))
xx = TheRng.Value
innerRow = 1

For i = UBound(xx) To 2 Step -1
    Do While ActiveSheet.Range("b" & Row) <> ""
        If ActiveSheet.Range("b" & Row) = ActiveSheet.Range("b" & innerRow) Then
            For j = i - 1 To 1 Step -1
         If ActiveSheet.Range("Z" & Row) < ActiveSheet.Range("Z" & innerRow) Then
       Set RowsToDelete = Union(IIf(RowsToDelete Is Nothing, TheRng.Cells(i, 1), RowsToDelete), TheRng.Cells(i, 1))
Exit For
End If
Next j
Loop
RowsToDelete.EntireRow.Select 'Delete
End Sub

yes , actually i still recommend my peer to do them but in case the data get bigger , i want to cover them in VBA — transontung90
– transontung90, Commented Mar 13, 2019 at 5:03
@SiddharthRout, how does AutoFilter exclude rows with the latest date in column Z for every identifier in column B? The linked example doesn't seem to cover that. — user11174618
– user11174618, Commented Mar 13, 2019 at 5:04

user11174618 · Accepted Answer · 2019-03-13 04:20:19Z

2

It looks like you want to delete duplicates from column B but keep the row with the newest date in column Z.

RemoveDuplicates typically deletes the duplicate with the largest row number (closest to the bottom of the data) so Sort the data with the date descending to leave the latest date at the top and remove duplicates.

Sub Macro1()

    With Worksheets("sheet1")
        'With .Range(.Cells(1, "B"), .Cells(.Rows.Count, "Z").End(xlUp))
        With Intersect(.UsedRange, .Range("B:Z"))

            .Sort key1:=.Columns(1), order1:=xlAscending, _
                  key2:=.Columns(.Columns.Count), order2:=xlDescending, _
                  Header:=xlNo

            .RemoveDuplicates Columns:=1, Header:=xlNo

        End With
    End With

End Sub

answered Mar 13, 2019 at 4:20

user11174618

Sign up to request clarification or add additional context in comments.

1 Comment

transontung90 Over a year ago

thanks you for your guidance , now i know both the solution and how remove duplicates work . thanks you so much

Collectives™ on Stack Overflow

How to delete duplicate row with condition in VBA

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related