none
How to extract Excel Autofilter.Filters.Items.Criteria1 Arrays in C#. RRS feed

  • Question

  • Hello,

    I'm attempting to grab the the array of criteria applied to a specific filtered column and return it to a list or array. I can count the number of objects in the array with:

    int cnt = sht.AutoFilter.Filters.Item[a].Count;

    where a is part of a for loop that loops through each "Filter" in the collection for the current "AutoFilter".  I've tried various ways to extract a list/array of the Criteria1 item but have been unsuccessful for a few days now.  I've tried adding one at a time with a for loop using the count, I've tried adding the range to a list of objects, I've tried assigning to an object array and even just calling it to a message box directly but have been unsuccessful.  Does anyone have any experience with this?

    Note that - when the autofilter only contains one item, you can simply return a string of the object like this:

    MessageBox.Show(sht.AutoFilter.Filters.Item[a].Criteria1);

    Lastly, just to provide higher context, I built this in VBA and I'm attempting to create a similar build in a VSTO addin.

    Sub FilterInformation()
        Dim st As String, ws As Worksheet, rg As Range, boo As Boolean
    
        Set ws = ActiveSheet
        On Error GoTo GetMeOut
        Set rg = ws.AutoFilter.Range
    
        MsgBox "Filter range" & vbCrLf & rg.Address
    
        N = ws.AutoFilter.Filters.Count
        MsgBox "Number of filters" & vbCrLf & N
    
        For i = 1 To N
            boo = ws.AutoFilter.Filters.Item(i).On
            MsgBox i & "==>" & boo
            If boo Then
                MsgBox UBound(ws.AutoFilter.Filters.Item(i).Criteria1) & " items in array"
                U = UBound(ws.AutoFilter.Filters.Item(i).Criteria1)
                L = LBound(ws.AutoFilter.Filters.Item(i).Criteria1)
                For j = L To U
                    MsgBox ws.AutoFilter.Filters.Item(i).Criteria1(j)
                Next
            End If
    
        Next
        Exit Sub
    GetMeOut:
        MsgBox ("no filters in sheet")
    End Sub

    This is my first post here so let me know if I did something wrong as well. Cheers!

    Monday, May 8, 2017 12:51 PM

All replies

  • Hi RyanClouse,

    you can try to refer thread below in which another user had mentioned how to access filtered data.

    Reading Filtered data from Excel in C#

    also you can try to first filter the data.

    then you can try to access data from that column and remove duplicate data from the list.

    so at the end you can get the list of filtered data.

    Sub demo()
    Dim tmp As String
    Dim arr() As String
    Dim cell As Range
    If Not Selection Is Nothing Then
       For Each cell In Selection
          If (cell <> "") And (InStr(tmp, cell) = 0) Then
            tmp = tmp & cell & "|"
          End If
       Next cell
    End If
    
    If Len(tmp) > 0 Then tmp = Left(tmp, Len(tmp) - 1)
    
    arr = Split(tmp, "|")
    End Sub
    

    you can convert the code to C#.

    Regards

    Deepak


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Tuesday, May 9, 2017 6:43 AM
    Moderator
  • Thanks Deepak, the workaround you suggested is going to by my approach if I can't figure how to access the array. Just worried about that potentially breaking in some special instances. I'll check into the link you mentioned though, thanks!
    Tuesday, May 9, 2017 1:11 PM