How to Autofilter Excel rows based on one cell value

Sujeet Raman

Hi, I have to filter some excel rows based on on cell value like if the coloumn value,ID 1, i need to display all rows ID is not equal to 1

is there anything like this??

excelRange.AutoFilter(9, cellvalue !="1", Excel.XlAutoFilterOperator.xlFilterValues, Type.Missing, true);

4 Replies

Know the answer? Post it — somebody with the same question will find it here.

Sujeet Raman5y ago
xlWorksheetNew.UsedRange.AutoFilter(9, "<>01", XlAutoFilterOperator.xlAnd); not working
+1
Sujeet Raman5y ago
hi i need to do complete delete of a row other than filter .My code is not deleting the id=1 i dont know what happend.code is excecuting without any error

for (int i = 2; i <= Rows; i++)
{
var DepDel1 = (Excel.Range)xlWorksheetNew.Cells[i, 9];
string DepDel = Convert.ToString(DepDel1.Value2);
Excel.Range r = xlWorksheetNew.Range[xlWorksheetNew.Cells[i, 1], xlWorksheetNew.Cells[i, 10]];
if (DepDel == "1")
{
// if match, delete and shift remaining cells up:
r.EntireRow.Delete(Microsoft.Office.Interop.Excel.XlDeleteShiftDirection.xlShiftUp);
// (xlWorksheetNew.Rows[i] as Microsoft.Office.Interop.Excel.Range).Delete();
}
}

+1
Ganesan C5y ago
xl_WorkSheet.UsedRange.AutoFilter(9, "<>1", XlAutoFilterOperator.xlAnd);
+1

Leon D5y ago

Hi,

You can install Free Spire.XLS library to achieve this.

First, install it via nuget.

https://www.nuget.org/packages/FreeSpire.XLS/

Second, add the following code:

using Spire.Xls;
using Spire.Xls.Collections;

namespace CustomFilter
{
    class Program
    {
        static void Main(string[] args)
        {
            Workbook workbook = new Workbook();
            workbook.LoadFromFile(@"test.xlsx");
            Worksheet sheet = workbook.Worksheets[0];

            AutoFiltersCollection filters = sheet.AutoFilters;
            filters.Range = sheet.Range;

            for (int col = 0; col < sheet.LastColumn; col++)
            {
                filters.CustomFilter(col, Spire.Xls.Core.Spreadsheet.AutoFilter.FilterOperatorType.NotEqual, 1);
            }

            filters.Filter();
            workbook.SaveToFile("output.xlsx", ExcelVersion.Version2013);
        }
    }
}