none
Excel AutoFilter function in Visual Basic

    Question

  • I'm writing a small program to manipulate an Excel spreadsheet.

    I need to be able to AutoFilter a certain 'list' of telephone extensions in my company.

    I.E.

    I'd like to sort by three extensions, lets say, 116, 119, and 122.

    This is exceptionally easy inside of Excel as:

     ActiveSheet.Range("$A$1:$N$34").AutoFilter Field:=1, Criteria1:=Array("116" _
      , "119", "122"), Operator:=xlFilterValues
     Application.Goto Reference:="Macro1"

    However, I cannot get this to work in Visual Basic 2010.

    I tried the following:

     

    Dim myAl(0 To 2) As String
      myAl(0) = "116"
      myAl(1) = "119"
      myAl(2) = "122"
    
      oSheet.ListObjects.AddEx(Excel.XlListObjectSourceType.xlSrcRange, oApp.Range("$A:$H"), , Excel.XlYesNoGuess.xlYes).Name = "Calls"
    
      oSheet.ListObjects("Calls").Range.AutoFilter(Field:=1, Criteria1:=myAl)

    However this resulted in the Excel sheet only filtering the final entry in the array.

    Anyone able to offer some insight into what I'm doing wrong?

    • Moved by Liliane Teng Monday, July 26, 2010 3:12 AM Please repost on appropriate forum for better support (From:Visual Basic General)
    Saturday, July 24, 2010 4:53 PM

Answers

  • Not that I know the first thing about manipulation of Excel, but maybe try to declare your array differently:

    Dim myAl() As String = {"116", "119", "122"}
    
    Saturday, July 24, 2010 5:24 PM
  • I'm writing a small program to manipulate an Excel spreadsheet.

    I need to be able to AutoFilter a certain 'list' of telephone extensions in my company.

    I.E.

    I'd like to sort by three extensions, lets say, 116, 119, and 122.

    This is exceptionally easy inside of Excel as:

     ActiveSheet.Range("$A$1:$N$34").AutoFilter Field:=1, Criteria1:=Array("116" _
    
     , "119", "122"), Operator:=xlFilterValues
    
     Application.Goto Reference:="Macro1"

    However, I cannot get this to work in Visual Basic 2010.

    I tried the following:

     

    Dim myAl(0 To 2) As String
    
     myAl(0) = "116"
    
     myAl(1) = "119"
    
     myAl(2) = "122"
    
    
    
     oSheet.ListObjects.AddEx(Excel.XlListObjectSourceType.xlSrcRange, oApp.Range("$A:$H"), , Excel.XlYesNoGuess.xlYes).Name = "Calls"
    
    
    
     oSheet.ListObjects("Calls").Range.AutoFilter(Field:=1, Criteria1:=myAl)

    However this resulted in the Excel sheet only filtering the final entry in the array.

    Anyone able to offer some insight into what I'm doing wrong?


    Hi

    Can you try the following:

    oSheet.ListObjects("Calls").Range.AutoFilter(Field:=1, Criteria1:=myAl, Operator:=xlFilterValues)

    Cheers

    Shasur


    http://www.vbadud.blogspot.com
    Wednesday, July 28, 2010 2:46 AM

All replies

  • Not that I know the first thing about manipulation of Excel, but maybe try to declare your array differently:

    Dim myAl() As String = {"116", "119", "122"}
    
    Saturday, July 24, 2010 5:24 PM
  • Unfortunately that still doesn't work.
    Saturday, July 24, 2010 6:26 PM
  • Unfortunately that still doesn't work.

    So much for that thought :-\
    Saturday, July 24, 2010 6:27 PM
  • I appreciate your help though.
    Sunday, July 25, 2010 5:05 AM
  • Hello Jordan,

    Thanks for your post.

    I have moved your thread from Visual Basic General to VBA forum where you could get better and quicker support because your problem may be related to VBA. Thanks for understanding.

    Best regards,
    Liliane


    Please mark the replies as answers if they help and unmark them if they provide no help. Thanks
    Monday, July 26, 2010 3:14 AM
  • I'm writing a small program to manipulate an Excel spreadsheet.

    I need to be able to AutoFilter a certain 'list' of telephone extensions in my company.

    I.E.

    I'd like to sort by three extensions, lets say, 116, 119, and 122.

    This is exceptionally easy inside of Excel as:

     ActiveSheet.Range("$A$1:$N$34").AutoFilter Field:=1, Criteria1:=Array("116" _
    
     , "119", "122"), Operator:=xlFilterValues
    
     Application.Goto Reference:="Macro1"

    However, I cannot get this to work in Visual Basic 2010.

    I tried the following:

     

    Dim myAl(0 To 2) As String
    
     myAl(0) = "116"
    
     myAl(1) = "119"
    
     myAl(2) = "122"
    
    
    
     oSheet.ListObjects.AddEx(Excel.XlListObjectSourceType.xlSrcRange, oApp.Range("$A:$H"), , Excel.XlYesNoGuess.xlYes).Name = "Calls"
    
    
    
     oSheet.ListObjects("Calls").Range.AutoFilter(Field:=1, Criteria1:=myAl)

    However this resulted in the Excel sheet only filtering the final entry in the array.

    Anyone able to offer some insight into what I'm doing wrong?


    Hi

    Can you try the following:

    oSheet.ListObjects("Calls").Range.AutoFilter(Field:=1, Criteria1:=myAl, Operator:=xlFilterValues)

    Cheers

    Shasur


    http://www.vbadud.blogspot.com
    Wednesday, July 28, 2010 2:46 AM
  • It seems you are declaring the array as a string:

    Dim myAl(0 To 2) As String
     myAl(0) = "116"
     myAl(1) = "119"
     myAl(2) = "122

    Try to declare as variant (=array):

    Dim myAl(0 To 2) As Variant
      myAl(0) = "116" myAl(1) = "119" myAl(2) = "122"
    Sunday, October 03, 2010 4:33 PM