title: AutoFilter method of Range class failed in VB.NET
body: <p>I am trying to use some Parsing i was able to tweak a little.  If I use it in straight VBA in excel, it works fine.  However, when I use the same code as a module in VB.NET I get the error in the title on the line of code </p>
  
  <blockquote>
    <p>ws.Range(vTitles).AutoFilter()</p>
  </blockquote>
  
  <p>(duh!)  I am not sure what is going wrong in the conversion, since I am not a hardcore VB.Net programmer, so I am doing a lot of googling, but not finding much that works.  Any ideas on how this could be fixed or do I have to abandon the idea of using this snippet in VB.Net?</p>
  
  <p>Here is the code I am using:</p>
  
  <pre><code>'turned strict off or autofilter per     http://www.pcreview.co.uk/threads/autofilter-method-of-range-class-failed.3994483/
  Option Strict Off
  Imports xl = Microsoft.Office.Interop.Excel
  
  Module ParseItems
  
  Public Sub ParseItems(ByRef fileName As String)
      'Jerry Beaucaire  (4/22/2010)
      'Based on selected column, data is filtered to individual workbooks are named for the value plus today's date
      Dim wb As xl.Workbook
      Dim xlApp As xl.Application
      Dim LR As Long, Itm As Long, MyCount As Long, vCol As Long
      Dim ws As xl.Worksheet, MyArr As Object, vTitles As String, SvPath As String
  
      'Set new application and make wb visible
      xlApp = New xl.Application
      xlApp.Visible = True
  
      'open workbook
      wb = xlApp.Workbooks.Open(fileName)
  
  
      'Sheet with data in it
      ws = wb.Sheets("Original Data")
  
      'Path to save files into, remember the final "\"
      SvPath = "G:\MC VBA test\"
  
      'Range where titles are across top of data, as string, data MUST have titles in this row, edit to suit your titles locale
      vTitles = "A1:L1"
  
      'Choose column to evaluate from, column A = 1, B = 2, etc.
      vCol = xlApp.InputBox("What column to split data by? " &amp; vbLf &amp; vbLf &amp; "(A=1, B=2, C=3, etc)", "Which column?", 1, Type:=1)
      If vCol = 0 Then Exit Sub
  
      'Spot bottom row of data
      LR = ws.Cells(ws.Rows.Count, vCol).End(xl.XlDirection.xlUp).Row
  
      'Speed up macro execution
      'Application.ScreenUpdating = False
  
      'Get a temporary list of unique values from key column
      ws.Columns(vCol).AdvancedFilter(Action:=xl.XlFilterAction.xlFilterCopy, CopyToRange:=ws.Range("EE1"), Unique:=True)
  
      'Sort the temporary list
      ws.Columns("EE:EE").Sort(Key1:=ws.Range("EE2"), Order1:=xl.XlSortOrder.xlAscending, Header:=xl.XlYesNoGuess.xlYes, _
         OrderCustom:=1, MatchCase:=False, Orientation:=xl.Constants.xlTopToBottom, DataOption1:=xl.XlSortDataOption.xlSortNormal)
  
      'Put list into an array for looping (values cannot be the result of formulas, must be constants)
      MyArr = xlApp.WorksheetFunction.Transpose(ws.Range("EE2:EE" &amp; ws.Rows.Count).SpecialCells(xl.XlCellType.xlCellTypeConstants))
  
      'clear temporary worksheet list
      ws.Range("EE:EE").Clear()
  
      'Turn on the autofilter, one column only is all that is needed
      ws.Range(vTitles).AutoFilter()
  
      'Loop through list one value at a time
      For Itm = 1 To UBound(MyArr)
          ws.Range(vTitles).AutoFilter(Field:=vCol, Criteria1:=MyArr(Itm))
  
          ws.Range("A1:A" &amp; LR).EntireRow.Copy()
          xlApp.Workbooks.Add()
          ws.Range("A1").PasteSpecial(xl.XlPasteType.xlPasteAll)
          ws.Cells.Columns.AutoFit()
          MyCount = MyCount + ws.Range("A" &amp; ws.Rows.Count).End(xl.XlDirection.xlUp).Row - 1
  
          xlApp.ActiveWorkbook.SaveAs(SvPath &amp; MyArr(Itm), xl.XlFileFormat.xlWorkbookNormal)
          'ActiveWorkbook.SaveAs SvPath &amp; MyArr(Itm) &amp; Format(Date, " MM-DD-YY") &amp; ".xlsx", 51   'use for Excel 2007+
          xlApp.ActiveWorkbook.Close(False)
  
          ws.Range(vTitles).AutoFilter(Field:=vCol)
      Next Itm
  
      'Cleanup
      ws.AutoFilterMode = False
      MsgBox("Rows with data: " &amp; (LR - 1) &amp; vbLf &amp; "Rows copied to other sheets: " &amp; MyCount &amp; vbLf &amp; "Hope they match!!")
      xlApp.Application.ScreenUpdating = True
  End Sub
  
  
  
  End Module
  </code></pre>