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

see [question get last filled row](https://stackoverflow.com/questions/7674573/programmatically-getting-the-last-filled-excel-row-using-c-sharp) — Ivan.s, Apr 13 '20 at 07:11
Yesyou can find the last row as shown [HERE](https://stackoverflow.com/questions/11169445/error-in-finding-last-used-cell-in-excel-with-vba/11169920#11169920) and then construct your range. — Siddharth Rout, Apr 13 '20 at 07:19
@SiddharthRout but when i replaced the End(xlDown) in Set rng = Range("A1", Range("E1").End(xlDown)) with End(xlUp), nothing happens... — john, Apr 13 '20 at 07:21
Wow that was fast. You managed to read and understand the entire post in less than a minute? :) I would recommend reading it once again... this time more slowly — Siddharth Rout, Apr 13 '20 at 07:22
Find the last row and then use the range as `Range("A1:E" & LastRow)` — Siddharth Rout, Apr 13 '20 at 07:24
@SiddharthRout it works, thanks. But can i ask what is the meaning of rng.RemoveDuplicates Columns:=Array(1, 2) in this case? Because even when i put it as = Array(1,2,3,4,5) or =Array(1) for example, it still works. — john, Apr 13 '20 at 07:41
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, Apr 13 '20 at 07:47

score 0 · Answer 1 · answered Apr 13 '20 at 16:28

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 Answers1