dynamic column range to removeduplicates

[sandysharma]

Hi Everyone,

I am trying to remove duplicate rows from an excel sheet using the following code:

activesheet.range("a1:d100").removeduplicates columns:=array(1,2,3,4), header:=xlyes

This code is running fine however I was wondering if there is any way that I dont mention the number of columns here and it should automatically take total number of columns in the range and remove duplicates based on total number of columns...because in this way I will have to amend the code if I add one more column to the data....Please suggest...

Great thanks in advance!!

Regards,
Sandeep

 

[Marc L]

Hi !

Just using Excel basics :​

    With [A1:D100]
        .RemoveDuplicates Columns:=Evaluate("COLUMN(" & .Address & ")"), Header:=xlYes
    End With

Do you like it ? So thanks to click on bottom right Like !
​

 

[jindon]

http://www.excelforum.com/excel-pro...column-array-value-from-cell.html#post4440335

Sub test()
    Dim x
    With [a1:d100]  '★Change to suite
        x = Evaluate("column(" & [a1].Resize(, .Columns.Count).Address & ")")
        ReDim Preserve x(0 To UBound(x) - 1)
        .RemoveDuplicates Columns:=(x), Header:=xlYes
    End With
End Sub

 

[sandysharma]

Hi Jindon

Thanks for quick help...this code is giving run time error 1004 application defined or object defined error on the below line...

.RemoveDuplicates Columns:=(x), Header:=xlYes

Please suggest..
Thanks.
Sandy

 

[jindon]

No idea about that error.
I need to see the workbook.

 

[sandysharma]

Hi Jindon,

I am sorry for the miss......It is working perfectly all right after referencing to activeworkbook.sheet(1)....

Thanks a ton!!

One question though...if you could please help me with the use of evaluate.....and where else can I use this....

 

[jindon]

Evaluate method is very handy sometimes.

1) Calculate a cell formula without actually insert to a cell.
e.g

MsgBox Evaluate("=Sum(A1:A2)")

2) Since it accepts 2 d array, it returns 1 based 2 d Array.
e.g

Dim x
x=Evaluate("row(1:5)")

gives you
x(1,1)=1
x(2,1)=2
x(3,1)=3
x(4,1)=4
x(5,1)=5

Google about "vba evaluate method" for more details.

 

[sandysharma]

Great!!

Thanks Jindon...this was a great help!!!

 

[Marc L]

I just mod post #2 'cause I could not test on my Excel 2003 version.

Can be directly done with address instead of columns.count
as you can see now in post #2 or like this :​

          AD$ = "A1:D100"
    Range(AD).RemoveDuplicates Columns:=Evaluate("COLUMN(" & AD & ")"), Header:=xlYes

Or like that :​

    [A1:D100].RemoveDuplicates Columns:=Evaluate("COLUMN(A1:D100)"), Header:=xlYes

Do you like it ? So thanks to click on bottom right Like !
​