Free Support Forum -

Visible cells and Autofilter


I want to apply Autofilter where cells contains a value. Also I want to get visible cells indices in an array. Please help me in applying filter where cells contains a value. Also the visible cells indices

cells = worksheet.Cells;
worksheet.AutoFilter.Range = “A1:D34655”;
worksheet.AutoFilter.Filter(1, “Total”);
//worksheet.AutoFilter.AddFilter(0, “Yes”);
int[] rowIndice1s = worksheet.AutoFilter.Refresh(false);


  1. Do you need to specify whole data range (upto the last data cell) in the worksheet? please elaborate with a sample file with complete details, we will help you through. Also provide your desired file which you may create manually in Ms Excel.

  2. Well, you may export to fill a data table based on the filtered rows (only visible rows will be included), you should specify ExportTableOptions, see the sample code for your reference:
    Sample code:

     Workbook workbook = new Workbook(@"e:\test2\Book1.xlsx");
                 Worksheet worksheet = workbook.Worksheets[0];
     //Your code goes here.
     Range displayRange = worksheet.Cells.MaxDisplayRange;
                 ExportTableOptions options = new ExportTableOptions();
                 options.ExportAsString = true;
                 options.ExportColumnName = true;
                 options.PlotVisibleCells = true;
                 options.PlotVisibleRows = true;
                 DataTable dataTable = worksheet.Cells.ExportDataTable(0, 0, displayRange.RowCount, displayRange.ColumnCount, options);
                 dataTable.TableName = "Data2";
                 workbook.Worksheets[1].Cells.ImportDataTable(dataTable, true, "A1");

Hope, this helps a bit.