Add multiple columns to range.RemoveDuplicates

Question

I am trying to remove duplicates from excel sheets by selecting all the cells. So I know how to work on one single column. But I want to do it with the whole sheet.

This is what I've tried:

private void RemoveDuplicateRecords()
    {
        try
        { 
            var ar = new int[0];

            foreach (Excel.Worksheet worksheet in bookDest.Worksheets)
            {
                if (worksheet.AutoFilter != null)
                    worksheet.AutoFilterMode = false;  

                int n = worksheet.UsedRange.Columns.Count;
                ar = new int[n];

                for (int i = 0; i < n;)
                {
                    ar[i] = ++i;
                }

                object cols = new object[] { ar }; //isn't working

                Excel.Range range = worksheet.UsedRange;
                range.Select();
                range.Activate();
                range.RemoveDuplicates(cols, Excel.XlYesNoGuess.xlYes);
                //Also tried:
                //range.RemoveDuplicates((object)ar, Excel.XlYesNoGuess.xlYes);  //but no luck
            }
        }
        catch(Exception e)
        {
            MessageBox.Show(e.Message);
        }
    }

I know I can do it something like this if I have a 5 columns: object cols = new object[]{1, 2, 3, 4, 5};

But I don't want to do it manually. I want to add columns dynamically. Excel files can have any no.of columns. Any help on this is appreciated. Thanks for the help.

https://stackoverflow.com/a/46724947/10344668 – TerribleDog Jan 31 '19 at 08:11 — TerribleDog, Jan 31 '19 at 08:11

score 1 · Accepted Answer · answered Jan 31 '19 at 09:24

I guess this should work:

    //To remove all the duplicate lines in the result file
    private void RemoveDuplicateRecords()
    {
        try
        {
            foreach (Excel.Worksheet worksheet in bookDest.Worksheets)
            {
                if (worksheet.AutoFilter != null)
                    worksheet.AutoFilterMode = false;

                long n = worksheet.UsedRange.Columns.Count;
                Excel.Range range = worksheet.UsedRange;
                range.Select();
                range.Activate();

                range.RemoveDuplicates(BuildColAry(n), Excel.XlYesNoGuess.xlYes);
            }
        }
        catch(Exception e)
        {
            MessageBox.Show(e.Message);
        }
    }

    dynamic BuildColAry(long n)
    {
        dynamic vMyArray = new dynamic[n];
        int idx;

        for (idx = 1; idx <= n; idx++)
        {
            vMyArray[idx - 1] = idx;
        }
        return vMyArray;
    }

Add multiple columns to range.RemoveDuplicates

1 Answers1