How do i delete duplicated rows in VBA?

Question

There is this old thread that provides a solution to delete duplicated rows, Delete all duplicate rows Excel vba .But if i use it on the data as shown below, that solution doesn't work as intended. Although i can add .End(xlDown) such that Set rng = Range("A1", Range("E1").End(xlDown).End(xlDown.End(xlDown) to delete the duplicated rows(Device A) that are separated in the screenshot, is there a better way to do it?

Another question that i want to clarify is what is the meaning of rng.RemoveDuplicates Columns:=Array(1, 2)? Because even if i put it as = Array(1) or even Array(1,2,3,4,5) i realised it will still delete the duplicated rows.

Sub test()


   With Worksheets("Output")
        Set rng = Range("A1", Range("E1").End(xlDown))
        rng.RemoveDuplicates Columns:=Array(1, 2), Header:=xlNo
    End With


End Sub

Yesyou can find the last row as shown HERE and then construct your range. — Siddharth Rout
@SiddharthRout but when i replaced the End(xlDown) in Set rng = Range("A1", Range("E1").End(xlDown)) with End(xlUp), nothing happens... — john
Find the last row and then use the range as Range("A1:E" & LastRow) — Siddharth Rout
Those are the columns that contain the duplicate information. Try this experiment. Record a macro and then select your range and then click on Data|Remove Duplicates. Now select A,C,E columns in the dialog box. Finish the process and then check the code. Repeat the process by selecting different columns and checking the code. That will make it clear on how this works :) — Siddharth Rout

Evil Blue Monkey Evil Blue Monkey · Accepted Answer · 2020-04-13T16:28:09

Assuming that each row with data has a non-blank cell in column E, you can set rng like this:

Set Rng = Range(Cells(1, 1), Cells(Range("A:A").Rows.Count, 5).End(xlUp))

You can also process the entire columns like this:

Range("A:E").RemoveDuplicates Columns:=Array(1, 2), Header:=xlNo

How do i delete duplicated rows in VBA?

1 Answers