none
Setting an Excel AutoFilter with an array(,) of values using VB.NET and Interop RRS feed

  • Question

  • In my shared Excel COM add-in, I'd like the user to be able to select a series of values down a single column and then have the AutoFilter set to only show rows where cells in the selected column are equal to any of the values contained in the Excel selection:

    Dim filterSheet As Excel.Worksheet = ExcelApplication.ActiveSheet
    Dim filterRange As Excel.Range = filterSheet.UsedRange

    Dim selectedRange As Excel.Range = ExcelApplication.Selection
    Dim selectedColumn As Integer = selectedRange.Column

    Dim selectedValues As Object = selectedRange.Value
    ' selectedValues is a 2 dimensional array(0 to # rows in the selection -1, 0 to 0)

    filterRange.AutoFilter(selectedColumn, selectedValues)

    This nearly works but it only sets the filter for the last value in the selectedValues(,) array.

    I also tried to transpose the array but again, only the last element in the array was applied to the AutoFilter:

    Dim transposedValues As Object = ExcelApplication.WorksheetFunction.Transpose(selectedValues)
    ' transposedValues is a 1 dimensional array(0 to # rows in the selection -1)

    filterRange.AutoFilter(selectedColumn, transposedValues)

    I've fooled around with some of the Excel.XlAutoFilterOperator values but that resulted in further confusion on my part.

    What do I need to do in order to persuade Excel to filter on all of the values in the selectedValues(,) array?

    Perhaps I'm using the wrong filterRange? (I'm trying to apply the filter criteria to the entire sheet)

    Thanks.

    Note: I'm testing in VS 2010 with Excel 2013 (x86) using PIA's for Excel 2007.


    • Edited by Ou8 Thursday, September 11, 2014 8:46 PM
    Thursday, September 11, 2014 8:43 PM

Answers

  • Hi Ou8,

    >>Dim selectedValues As Object = selectedRange.Value
    ' selectedValues is a 2 dimensional array(0 to # rows in the selection -1, 0 to 0)

    I also tried to transpose the array but again, only the last element in the array was applied to the AutoFilter:

    Dim transposedValues As Object = ExcelApplication.WorksheetFunction.Transpose(selectedValues)
    ' transposedValues is a 1 dimensional array(0 to # rows in the selection -1)

    filterRange.AutoFilter(selectedColumn, transposedValues)<<

     

    As far as I test, the Criteria1 parameter for the Range.AutoFilter couldn't accept the 2 dimensional array. To fix this issue, we need to convert it to one dimension. And we need to pass the Operator parameter when we use array.

    Here is an example that use a range to filter:

     Sub Main()
            Dim ExcelApp As New Application
            ExcelApp.Visible = True
            Dim aWorkBook As Workbook
            aWorkBook = ExcelApp.Workbooks.Open("C:\Users\UserName\Desktop\Test.xlsx")
    
            aWorkBook.Worksheets("Sheet1").Activate()
            aWorkBook.Worksheets("Sheet1").Range("I20:I22").Select()
            Dim fitlerValues() As String     
    
            ReDim fitlerValues(ExcelApp.Selection.Cells.Count - 1)
            Dim index As Integer
            index = 0
            For Each aCell In ExcelApp.Selection
                fitlerValues(index) = aCell.Value
                index = index + 1
            Next
            aWorkBook.Worksheets("Sheet1").Range("A1").AutoFilter(1, fitlerValues, XlAutoFilterOperator.xlFilterValues)
        End Sub
    Screenshot:

    Best regards

    Fei


    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, September 12, 2014 7:25 AM
    Moderator
  • Hi Ou8,

    >>Do you know if there's any way to do this in Excel 2003? The XlAutoFilterOperator.xlFilterValues doesn't seem to exist if I use the Excel 11 PIA's.<<

    Since the AutoFilter for Excel 2003 is different with Excel 2013, I suggest that you record a macro to get the correct parameter to pass.

    Note: the Office 2003 is end of support, there are some potential risks of staying with Office 2003. You can get more detail from link below:
    End of support

    Best regards

    Fei


    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.

    Monday, September 15, 2014 7:35 AM
    Moderator

All replies

  • Hi Ou8,

    >>Dim selectedValues As Object = selectedRange.Value
    ' selectedValues is a 2 dimensional array(0 to # rows in the selection -1, 0 to 0)

    I also tried to transpose the array but again, only the last element in the array was applied to the AutoFilter:

    Dim transposedValues As Object = ExcelApplication.WorksheetFunction.Transpose(selectedValues)
    ' transposedValues is a 1 dimensional array(0 to # rows in the selection -1)

    filterRange.AutoFilter(selectedColumn, transposedValues)<<

     

    As far as I test, the Criteria1 parameter for the Range.AutoFilter couldn't accept the 2 dimensional array. To fix this issue, we need to convert it to one dimension. And we need to pass the Operator parameter when we use array.

    Here is an example that use a range to filter:

     Sub Main()
            Dim ExcelApp As New Application
            ExcelApp.Visible = True
            Dim aWorkBook As Workbook
            aWorkBook = ExcelApp.Workbooks.Open("C:\Users\UserName\Desktop\Test.xlsx")
    
            aWorkBook.Worksheets("Sheet1").Activate()
            aWorkBook.Worksheets("Sheet1").Range("I20:I22").Select()
            Dim fitlerValues() As String     
    
            ReDim fitlerValues(ExcelApp.Selection.Cells.Count - 1)
            Dim index As Integer
            index = 0
            For Each aCell In ExcelApp.Selection
                fitlerValues(index) = aCell.Value
                index = index + 1
            Next
            aWorkBook.Worksheets("Sheet1").Range("A1").AutoFilter(1, fitlerValues, XlAutoFilterOperator.xlFilterValues)
        End Sub
    Screenshot:

    Best regards

    Fei


    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, September 12, 2014 7:25 AM
    Moderator
  • That seemed to do the trick. Thanks! It seems that passing in a string array is also important.

    Do you know if there's any way to do this in Excel 2003? The XlAutoFilterOperator.xlFilterValues doesn't seem to exist if I use the Excel 11 PIA's.

    Friday, September 12, 2014 6:22 PM
  • Hi Ou8,

    >>Do you know if there's any way to do this in Excel 2003? The XlAutoFilterOperator.xlFilterValues doesn't seem to exist if I use the Excel 11 PIA's.<<

    Since the AutoFilter for Excel 2003 is different with Excel 2013, I suggest that you record a macro to get the correct parameter to pass.

    Note: the Office 2003 is end of support, there are some potential risks of staying with Office 2003. You can get more detail from link below:
    End of support

    Best regards

    Fei


    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.

    Monday, September 15, 2014 7:35 AM
    Moderator