Sort data in Excel using the 'Auto Filter' using VB.net?

Question

I have AutoFilter in place in my sheet. I want to sort data using the AutoFilter itself and not by normal sort.

I want the client to see the down arrow on the autofilter button, which tells that rows are sorted on this key.

Any idea how to do that ?

I have 'sheet' as an object :

  sheet.Range("A2").AutoFilter then ??
  Or something else ??

Please help !

(Ensure the syntax for VB.Net and not VB Script)

I am a newbie..

Answer 1

To filter a range A1:A7, try:

Sheet.Range("$A$1:$A$7").AutoFilter(Field:=1, Criteria1:="MyFilter",         Operator:=XlAutoFilterOperator.xlFilterValues)

Make sure your are importing:

Imports Microsoft.Office.Interop.Excel

Edit:

sheet.AutoFilter.Sort.SortFields.Clear()
sheet.AutoFilter.Sort.SortFields.Add(Key:=sheet.Range("A1:A7"), SortOn:=XlSortOn.xlSortOnValues, Order:=XlSortOrder.xlAscending, DataOption:=xlSortDataOption.xlSortNormal)

With sheet.AutoFilter.Sort
    .Header = XlYesNoGuess.xlYes
    .MatchCase = False
    .Orientation = Constants.xlTopToBottom
    .SortMethod = XlSortMethod.xlPinYin
    .Apply()
End With