Worksheets("Sheet1").Range("A1").AutoFilter field:=1, Criteria1:="AMSIA", _
  VisibleDropDown:=False   ‘ hide the AutoFilter drop-down arrow. To unhide, set it to True

The following example sets the variable ‘c1’ to the value of the Criteria1 property of the filter for the first column in the filtered range on sheet1.

With Worksheets("sheet1")

    If .AutoFilterMode Then

        With .AutoFilter.Filters(1)

            If .On Then c1 = .Criteria1

        End With

    End If

End With

For this example, you must first make sure that all filters are off. It uses “IF...Then” statement to display the current status of the AutoFilterMode property on your active sheet. If the AutoFilterMode property is True, the next code would disactivate the AutoFilter in your active sheet.
If ActiveSheet.AutoFilterMode = True Then
   Selection.AutoFilter    ' or use ActiveSheet.AutoFilterMode = False
     If ActiveSheet.FilterMode = True Then
       ActiveSheet.ShowAllData
     End If
 End If



This example filters column A for unique values and copy to one after the last column on the same worksheet.
Dim LastRowcell As Range, LastColCell As Range
Set LastRowcell = Cells.Find("*", , , , xlByRows, xlPrevious)
Set LastColCell = Cells.Find("*", , , , xlByColumns, xlPrevious)
If Not LastRowcell Is Nothing Then
   Range(Cells(1, 1), Cells(LastRowcell.Row, 1)).AdvancedFilter _
   Action:=xlFilterCopy, CopyToRange:=Cells(1, LastColCell.Column + 1), _
   Unique:=True
End If

expression.AdvancedFilter(Action, CriteriaRange, CopyToRange, Unique) 

One of the useful features of AdvancedFilter is able to extract a unique list of one field from a range of dataset. The example shows how to obtain a unique list of Part Numbers from a dataset, knowing that Part Number is in column C. Of course, you should use Find method to locate C1 s ActiveCell so as not to hardcode it, but for this example, let’s bear with hardcoding C1. The unique list is copied to the last column but offset by 2, sort by ascending order. Add a header and calculate using R1C1 style formula, which is the equivalent of spreadsheet array function as “=sumproduct(($C$2:$D$30=F2)*($D$2:$D$30))”. Then it copies the array formula to all blank cells in column G (until the row in column F where unique list is blank). Diagram below is the exact output of what you should be seeing after this procedure is run. You can DOWNLOAD the worksheet example.

Sub GetUniqueList_AdvFilter()
Dim inRng As Range, outRng As Range
Dim finalrow As Long, finalcolumn2ToRight As Long
Dim finalrowUnq As Long, finalrowUnq2 As Long 

' Copy C1 heading to last cell in row 1, offset by 2 columns to the Right
‘ In a non-static user environment, you would want this value to be passed from input box or userform such as:
‘ i = InputBox("Enter the header address you want to be copied to")
‘ Then use this line Range(i).Copy Destination:=Cells(1, finalcolumn2ToRight)

Range("C1").Copy Destination:=Cells(1, finalcolumn2ToRight) 

' Define output range and input range
Set outRng = Cells(1, finalcolumn2ToRight)
Set inRng = Range("C1").Resize(finalrow, finalcolumn2ToRight - 2)

This property applies to the DataLabel, DataLabels, PivotField, Style, and TickLabels objects. For the PivotField object, you can set the NumberFormat property only for a Data area field. Take note that the Format function uses different format code strings than do the NumberFormat and NumberFormatLocal properties.

These examples below set the number format for cell A2:A1000, column 2, and column C to F on Sheet1.

Worksheets("Sheet1").Range("A2:A1000").NumberFormat = "General"
Worksheets("Sheet1").Columns(2).NumberFormat = "hh:mm:ss"

‘ Use a single comma and include “K” character suffix to indicate numbers are in thousands, eg. $12,345.6K
Worksheets("Sheet1").Columns("C").NumberFormat = "$#,##0.0,K"