RemoveDuplicates function - how can I set multiple columns?

Question

I'm trying to use the RemoveDuplicates function using Excel.Interop, but I'm stuck as to how to pass it the column array. I already know that I cannot pass it as a simple int[] array, as it gives an exception at runtime, and that I can pass a single integer and it works, but I want to be able to select which columns to use at runtime.

My current code in C# looks like this:

using Excel = Microsoft.Office.Interop.Excel;

private void removeDuplicates(Excel.Application excelApp, Excel.Range range, int[] columns)
{
    range.RemoveDuplicates(excelApp.Evaluate(columns), 
        Excel.XlYesNoGuess.xlNo); 
}

And it works fine if using only one column, but if the columns array has more than one value, only the first one is used.

In VBA, the equivalent function would be:

Sub RemoveBadExample()
    Dim colsToUse
    colsToUse = Array(1, 2)
    Selection.RemoveDuplicates Columns:=Evaluate(colsToUse), Header:=xlYes
End Sub

Which also fails to use both columns. however, if I change it to this:

    Selection.RemoveDuplicates Columns:=(colsToUse), Header:=xlYes

It works just fine. I guess my question then is what is the equivalent in C#?

What happens if you remove the use of Evaluate, just columns as the first argument? — Andy G, Jul 17 '13 at 18:26
If I use an `int` array and pass it as it is, I get the following exception: `System.Runtime.InteropServices.COMException: The remote procedure call failed. (Exception from HRESULT: 0x800706BE)` — Mario, Jul 19 '13 at 20:58

Ted · Accepted Answer · 2013-07-17T20:21:18.643

7

This test run worked for me in a unit test using 4.5 etc. It did not throw an exception at any rate.

        Application app = new Application();
        Workbook wb = app.Workbooks.Open("C:\\Data\\ABC.xlsx",
            Type.Missing, Type.Missing, Type.Missing, Type.Missing,
            Type.Missing, Type.Missing, Type.Missing, Type.Missing,
            Type.Missing, Type.Missing, Type.Missing, Type.Missing,
            Type.Missing, Type.Missing);
        Worksheet ws = wb.Sheets[1];
        Range rng = ws.Range["A1:C5", Type.Missing];
        object cols = new object[]{1, 2};
        rng.RemoveDuplicates(cols, XlYesNoGuess.xlYes);

Be aware that excel cell indices that define ranges are 1 based, not zero. So if you are passing in a bad range it will throw that kind of exception.

Object temp = range.Cells[1][1].Value;

edited Jul 17 '13 at 20:21

answered Jul 17 '13 at 18:42

Ted

3,212
25
20

I see some posts that do it like: Columns:= New Object() {1, 2, 3, 4} – Ted Jul 17 '13 at 19:54
Thanks! Using an `object` array instead of an `int` array it worked. – Mario Jul 17 '13 at 22:52
@Ted: Hi Ted! I know that's too late to ask but still. Can you please tell me how will I assign values to `cols` dynamically depending on the column count? Say if `sheet.UsedRange.Columns.Count` returns 20 than I want 20 entries. Can you please help? Sorry for the trouble. – StackUseR Jan 30 '19 at 12:56
Ashish, you can just do a for loop like int count = sheet.UsedRange.Columns.Count; object cols = new object[count]; for(int i = 0; i < count; i++){ object[i] = i+1; } and then pass it in as the parameter. – Ted Feb 07 '19 at 00:48

RemoveDuplicates function - how can I set multiple columns?

1 Answers1

Linked