0

enter image description here I want to remove/delete all duplicate rows from 5 columns which have no headers. I have recorded operation in macro and code is here:

Sub Macro11()
Columns("D:D").Select
Selection.SpecialCells(xlCellTypeConstants, 23).Select
ActiveSheet.Range("$A$1:$E$8").RemoveDuplicates Columns:=Array(1, 2, 3, 4, 5), _
    Header:=xlNo
Range("A1").Select
End Sub

I want above range to be variable as my count of rows will be varying. I have compared column 'C' to remove duplicates. It will be OK to compare each row or cells only from coulmn 'D'.

Samadhan Gaikwad
  • 186
  • 4
  • 21
  • 1
    You should look into the principle of lastrow. There are plenty of pieces of code around here that will help you. Then change your `ActiveSheet.Range()` into : `ActiveSheet.Range("$A$1:$E$"&lastrow)` – Luuklag Mar 15 '17 at 09:20
  • See http://stackoverflow.com/documentation/excel-vba/918/methods-for-finding-the-last-used-row-or-column-in-a-worksheet#t=201703150943548881299 and http://stackoverflow.com/a/11169920/4628637 (@Luuklag : it's better if you provide the link! ;) ) – R3uK Mar 15 '17 at 09:47
  • Thanks for letting me know this lastrow links! – Samadhan Gaikwad Mar 15 '17 at 10:01

1 Answers1

0

Below code is working fine to me: Sub Macro11() ActiveSheet.Range("A:E").RemoveDuplicates Columns:=4, Header:=xlNo End Sub

Samadhan Gaikwad
  • 186
  • 4
  • 21