none
How to delete from range repeating cells. RRS feed

  • Question

  • I work with excelApplication.Selection property. When user selected cells with ctrl button more than once (for example by selecting whole row and whole column at the same time), this cells will be in the selection twice.

    How can i solve this problem? Thank you.



    • Edited by Redrih Monday, June 11, 2012 10:30 AM
    Monday, June 11, 2012 8:27 AM

Answers

  • Redrih,

    You can still use events.

    To test the example that I am presenting here, create a new Excel 2010 Add-In project.

    Then, inside ThisAddIn class, add the following line to ThisAddIn_Startup method.

    Application.SheetSelectionChange += new Excel.AppEvents_SheetSelectionChangeEventHandler(Application_SheetSelectionChange);

    Then Add following handler method to your ThisAddIn class

            void Application_SheetSelectionChange(object Sh, Excel.Range Target)
            {
                //if necessary, check which one is the active sheet, for example,
                dynamic MySheet = (dynamic)Sh;
                string MySheetName = MySheet.Name;
                if (MySheetName != "MyWorkSheet")
                {
                    Debug.WriteLine(MySheetName);
                    //  Do something useful
                }
                //
                //If you want to access following SelectedCells variable outside this handler then comment out line below and declare it at the class level 
                List<Excel.Range> SelectedCells = new List<Excel.Range>();
                //If SelectedCells is declared at class level, empty it before proceeding further by uncommenting the following line
                //SelectedCells.Clear();
                //Loop through the selection and populate the list avoiding duplicate cells
                Debug.WriteLine("Selected cells");
                foreach (Excel.Range area in Target.Areas)
                {
                    foreach (Excel.Range cell in area)
                    {
                        Debug.WriteLine(cell.Row.ToString() + ", " + cell.Column.ToString());
                        if (!SelectedCells.Exists(r => r.Column == cell.Column && r.Row == cell.Row))
                            SelectedCells.Add(cell);
                    }
                }
                //
                Debug.WriteLine("Selected cells - duplicates removed");
                foreach (Excel.Range r in SelectedCells)
                    Debug.WriteLine(r.Row.ToString() + ", " + r.Column.ToString());
                //
                Debug.WriteLine("");
            }

    Since we are using Debug method, we need to add a using statement at the top

    using System.Diagnostics;

    Start debugging in visual studio and select multiple ranges using Shift and Ctrl keys. Let me know if you encounter any issue with the code.

    Hope this helps.

    kr

    Tuesday, June 12, 2012 2:33 PM

All replies

  • Could you describe the problem a bit more? The selection will not list cells multiple times.
    Monday, June 11, 2012 2:33 PM
  • Thank you for answer. Feel free to ask another questions.

    I develop add-in for excel, my addin get cells which user are selected and work with them.

    If user select the cells more the one time (like F5 on the screenshot) than property 'Selection' in the Excel.Application class, will be contains this cells more then once. So i need to delete this duplicate cells. How can I do it?

    Monday, June 11, 2012 2:44 PM
  • Thanks for the screenshot.

    Could you paste the code which you're using to access the cells, or describe it? Is it a foreach loop?

    • Edited by JosephFox Monday, June 11, 2012 4:57 PM
    Monday, June 11, 2012 4:56 PM
  • Redrih,

    I don't think there is a Range method that can remove dupliates in the selected Areas. If it is necessary to remove duplicate cells from the the selected Areas, you need to manage your own list of cells in the selected Areas, may be something like List<Execel.Range>. However, Cells in a simple list of this type will not keep reference to individual Area that they were part of in selected Areas.

    kr.

    Monday, June 11, 2012 5:56 PM
  • At first I use 'Selection' property of the Excel.Application property.

    Then I use 'Intersect' method with parameters 'Selection' and 'Selection.Worksheet.UsedRange' and then i use foreach for MyRange.Cells property.

    Monday, June 11, 2012 6:38 PM
  • Redrih,

    Is it a reply to me? If so, are you saying that you found the solution to you problem? Anyway, I thought I would add a bit detail of what I suggested earlier.

    Why are you using Application.Selection? This, as I understand, could return any type of object and not specific to Excel.Range object.

    You can use SelectionChange event handler of worksheet (Assuming you name your handler as Sheet1_SelectionChange)

    this.SelectionChange += new Excel.DocEvents_SelectionChangeEventHandler(Sheet1_SelectionChange);

    Then your handler will have immediate access to all the cells contained in the selection:

    void Sheet1_SelectionChange(Excel.Range Target)
    {
        int AreaIndex = 0;
        Debug.WriteLine("*** New Selection");
        foreach (Excel.Range r in Target.Areas)
        {
            Debug.WriteLine("Area " + (++AreaIndex).ToString("##"));
            foreach (Excel.Range s in r)
            {
                Debug.WriteLine("(" + s.Cells.Row.ToString() + "," + s.Cells.Column.ToString() + ")");
            }
        }
        Debug.WriteLine("");
               
    }
                

    Here in this handler you can update your list of cells and avoid duplicates.
    Hope this helps.

    kr

    Monday, June 11, 2012 8:11 PM
  • Khalique, my answer was adress to JosephFox. Unfortunately my add-in will start by user, so I cannot use events. How can I use List<Execel.Range> to solve my problem?
    • Edited by Redrih Monday, June 11, 2012 9:01 PM
    Monday, June 11, 2012 9:01 PM
  • I gotta go now. It is 5:00 pm here. I will prepare and test an example and post it for you tomorrow.

    kr

    Monday, June 11, 2012 9:59 PM
  • Redrih,

    You can still use events.

    To test the example that I am presenting here, create a new Excel 2010 Add-In project.

    Then, inside ThisAddIn class, add the following line to ThisAddIn_Startup method.

    Application.SheetSelectionChange += new Excel.AppEvents_SheetSelectionChangeEventHandler(Application_SheetSelectionChange);

    Then Add following handler method to your ThisAddIn class

            void Application_SheetSelectionChange(object Sh, Excel.Range Target)
            {
                //if necessary, check which one is the active sheet, for example,
                dynamic MySheet = (dynamic)Sh;
                string MySheetName = MySheet.Name;
                if (MySheetName != "MyWorkSheet")
                {
                    Debug.WriteLine(MySheetName);
                    //  Do something useful
                }
                //
                //If you want to access following SelectedCells variable outside this handler then comment out line below and declare it at the class level 
                List<Excel.Range> SelectedCells = new List<Excel.Range>();
                //If SelectedCells is declared at class level, empty it before proceeding further by uncommenting the following line
                //SelectedCells.Clear();
                //Loop through the selection and populate the list avoiding duplicate cells
                Debug.WriteLine("Selected cells");
                foreach (Excel.Range area in Target.Areas)
                {
                    foreach (Excel.Range cell in area)
                    {
                        Debug.WriteLine(cell.Row.ToString() + ", " + cell.Column.ToString());
                        if (!SelectedCells.Exists(r => r.Column == cell.Column && r.Row == cell.Row))
                            SelectedCells.Add(cell);
                    }
                }
                //
                Debug.WriteLine("Selected cells - duplicates removed");
                foreach (Excel.Range r in SelectedCells)
                    Debug.WriteLine(r.Row.ToString() + ", " + r.Column.ToString());
                //
                Debug.WriteLine("");
            }

    Since we are using Debug method, we need to add a using statement at the top

    using System.Diagnostics;

    Start debugging in visual studio and select multiple ranges using Shift and Ctrl keys. Let me know if you encounter any issue with the code.

    Hope this helps.

    kr

    Tuesday, June 12, 2012 2:33 PM