none
Problem with getting selected Range of filtered cells RRS feed

  • Question

  • Hi everyone, I am currently trying to fill selected filtered cells with data using:

    Excel.Range rngSelection = (Excel.Range)Globals.ThisAddIn.Application.Selection;

    var rngSelectionVisible = rngSelection.SpecialCells(Excel.XlCellType.xlCellTypeVisible, Type.Missing);   

     foreach (Excel.Range ActiveCell in rngSelectionVisible.Cells)
                        {

    //fill table

    }

    This work perfectly when certain rows are hidden, however does not work for filtered visible values at all. 

    I have also tried to apply an Autofilter:

    Excel.Range rngSelection = (Excel.Range)Globals.ThisAddIn.Application.Selection;
           
                    Excel.Range headers = WS.get_Range(rngSelection.ListObject.Name + "[#Headers]");

                    int indexColumn = HelperFunction.GetColumnIndexListObjectPropertyName(headers, ConstCustom.PropertyColumn.CODE);

                    Excel.Range rngSelectionVisible = WS.AutoFilter.Range.Columns[indexColumn].SpecialCells(Excel.XlCellType.xlCellTypeVisible, Type.Missing);  

     foreach (Excel.Range ActiveCell in rngSelectionVisible.Cells)
                        {

    //fill table

    }

    and the result was that the filtered cells where filled with data. However not the selected ones, but the ones from the entire column with the index [indexcolumn]. How can I get the selected range of cells with filter applied in Excel?

    Thursday, December 18, 2014 10:29 AM

Answers

  • How can I get the selected range of cells with filter applied in Excel?

    Get the intersection of the Selection and the AutoFilter.Range and the visible cells.

    I know nothing about C, I can only guess that the code could look similar like this:

    Excel.Range rngResult = Globals.ThisAddIn.Application.Intersect(
    WS.AutoFilter.Range.SpecialCells(Excel.XlCellType.xlCellTypeVisible, Type.Missing),
      Globals.ThisAddIn.Application.Selection);

    You also have to install an error handler, because maybe the selection is outside of the Autofilter.Range or the Autofilter hides all cells. Then walk through the rows like this:

    // for each row
    for each (Excel.Range ThisRow in rngResult.Rows)
    {
      // for each cell in this row
      for each (Excel.Range ThisCell in ThisRow.Cells)
      {
      //fill table
      }
    }

    I hope this is helpful.

    Andreas.

    • Marked as answer by Maciej_SS Friday, December 19, 2014 8:35 AM
    Thursday, December 18, 2014 11:18 AM

All replies

  • How can I get the selected range of cells with filter applied in Excel?

    Get the intersection of the Selection and the AutoFilter.Range and the visible cells.

    I know nothing about C, I can only guess that the code could look similar like this:

    Excel.Range rngResult = Globals.ThisAddIn.Application.Intersect(
    WS.AutoFilter.Range.SpecialCells(Excel.XlCellType.xlCellTypeVisible, Type.Missing),
      Globals.ThisAddIn.Application.Selection);

    You also have to install an error handler, because maybe the selection is outside of the Autofilter.Range or the Autofilter hides all cells. Then walk through the rows like this:

    // for each row
    for each (Excel.Range ThisRow in rngResult.Rows)
    {
      // for each cell in this row
      for each (Excel.Range ThisCell in ThisRow.Cells)
      {
      //fill table
      }
    }

    I hope this is helpful.

    Andreas.

    • Marked as answer by Maciej_SS Friday, December 19, 2014 8:35 AM
    Thursday, December 18, 2014 11:18 AM
  • Hi Maciej,

    First, please refer to Andreas’s reply.

    Secondly, to deal with that issue, you could iterate the Range object directly, then you could get all visible cells.

       Excel.Range rngSelectionVisible = WS.AutoFilter.Range.Columns[indexColumn].SpecialCells(Excel.XlCellType.xlCellTypeVisible, Type.Missing);   
    
    
     foreach (Excel.Range ActiveCell in rngSelectionVisible)
                {
    string v=ActiveCell.Value;
    //fill table
    
    }
    

    Best Regards

    Starain


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Friday, December 19, 2014 6:49 AM
    Moderator
  • Thanks, intersection was what I was looking for.

    Kind regards,

    Maciej

    Friday, December 19, 2014 8:35 AM