C# Office.Interop.Excel How do I turn ON AutoFilter for Cols 1, 2 & 3?

Alvord, Timothy 276 Reputation points
2023-07-31T21:31:23.31+00:00

I have three columns in my table. All I want to do is to turn on AutoFilter for the three columns. No Criteria...

Excel
Excel
A family of Microsoft spreadsheet software with tools for analyzing, charting, and communicating data.
2,144 questions
C#
C#
An object-oriented and type-safe programming language that has its roots in the C family of languages and includes support for component-oriented programming.
11,283 questions
0 comments No comments
{count} votes

Accepted answer
  1. Jiale Xue - MSFT 48,866 Reputation points Microsoft Vendor
    2023-08-01T02:47:44.8466667+00:00

    Hi @Alvord, Timothy , Welcome to Microsoft Q&A.

    Use Range.AutoFilter(Object, Object, XlAutoFilterOperator, Object, Object) Method (Microsoft.Office.Interop.Excel).

    You can refer to my code below:

    using System;
    using Excel = Microsoft.Office.Interop.Excel;
    
    namespace _8_xx_xx
    {
        internal class Program
        {
            // Helper method to release COM objects.
            private static void ReleaseObject(object obj)
            {
                try
                {
                    if (obj != null)
                    {
                        System.Runtime.InteropServices.Marshal.ReleaseComObject(obj);
                        obj = null;
                    }
                }
                catch (Exception ex)
                {
                    obj = null;
                    Console.WriteLine($"Error releasing COM object: {ex.Message}");
                }
                finally
                {
                    GC.Collect();
                }
            }
            static void Main(string[] args)
            {
                //Your own file path
                string filePath = @"C:\Users\Administrator\Desktop\xxx.xlsx";
                Excel.Application excelApp = new Excel.Application();
                Excel.Workbook workbook = null;
                Excel.Worksheet worksheet = null;
                try
                {
                    workbook = excelApp.Workbooks.Open(filePath);
                    worksheet = workbook.Worksheets[1]; // Assuming you want to work with the first sheet.
                    worksheet.AutoFilterMode = false;
                    //How to open the filter of all columns of the worksheet
                    worksheet.Range["A1", "D1"].AutoFilter(1, "<>0", Excel.XlAutoFilterOperator.xlAnd, "<>0", true); ;
                    // Save the changes (applying the AutoFilter).
                    workbook.Save();
                }
                catch (Exception ex)
                {
                    // Handle the exception or print the error message.
                    Console.WriteLine($"An error occurred: {ex.Message}");
                }
                finally
                {
                    // Clean up.
                    if (workbook != null)
                    {
                        workbook.Close();
                    }
    
                    excelApp.Quit();
                    ReleaseObject(worksheet);
                    ReleaseObject(workbook);
                    ReleaseObject(excelApp);
                }
                //Just to keep the console open.
                System.Console.ReadLine();
            }
        }
    }
    

    Choose your own rules for your screening.

    Mine is to filter the data in the range "A1" to "D1" of the worksheet according to the condition that the value of the first column (column A) is not equal to 0, and apply the filter results to the entire worksheet.

    enter image description here

    Best Regards,

    Jiale


    If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment". 

    Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

    1 person found this answer helpful.

0 additional answers

Sort by: Most helpful

Your answer

Answers can be marked as Accepted Answers by the question author, which helps users to know the answer solved the author's problem.