$Filename = "c:\temp\mysheet.xlsx"
$objExcel = New-Object -ComObject Excel.Application
$objExcel.DisplayAlerts = $False
$objWrkBk  = $objExcel.Workbooks.Open($Filename)
$objExcel.Visible = $True
# one way of selecting a sheet e.g. 3rd worksheet
# $objWrkBk.Sheets(3).Select()
# another way of selecting a sheet e.g. 1st worksheet
# $objWrkBk.Worksheets.item(1).Select()
$objWrkSht1 = $objWrkBk.Worksheets.item(1)
$objWrkSht1.Select()
# filter on 7th column, with string matching 'ABC'
$objWrkSht1.Range('$A$nn:$P$mm').AutoFilter(7,'=ABC')
# $A$nn => range first cell position, $P$mm = range last cell position

# select all used cells
$objExcel.Cells.Select()

1175
11

# Autofilter with multiple criteria on a specific column
$Filename = "c:\temp\mysheet.xlsx"
$objExcel = New-Object -ComObject Excel.Application
$objExcel.DisplayAlerts = $False
$objWrkBk  = $objExcel.Workbooks.Open($Filename)
$objExcel.Visible = $True

$objWrkSht1 = $objWrkBk.Worksheets.item(1)
$objWrkSht1.Select()
$objRange       = $objWrkSht1.UsedRange

$CriteriaArray = @('CA03','CY02','DEF02',"VA02","=")
# select blank with "=" OR ""
#$CriteriaArray = @('CA03','CY02','DEF02',"VA02")
$xlFilterValues = 7
$objRange.AutoFilter(7,$CriteriaArray,$xlFilterValues)

# More complex autofilters
$Filename = "c:\temp\mysheet.xlsx"
$objExcel = New-Object -ComObject Excel.Application
$objExcel.DisplayAlerts = $False
$objWrkBk  = $objExcel.Workbooks.Open($Filename)
$objExcel.Visible = $True

$objWrkSht1 = $objWrkBk.Worksheets.item(1)
$objWrkSht1.Select()
$objRange       = $objWrkSht1.UsedRange

$xlFilterValues = 7
$xlAnd = 1
$xlOr  = 2
# single criteria
$Criteria = "<>ABC01"   # Not Equal, use with xlAnd
$Criteria = "=ABC*"     # Begins with, use with xlAnd
$Criteria = "=*02"      # Ends with, use with xlAnd
$Criteria = "=*BC*"     # Contains, use with xlAnd
$Criteria = "<>*BC*"    # Does Not Contain, use with xlAnd
$objRange.AutoFilter(7,$Criteria,$xlAnd)
# 2 Criteria with logical And
$Criteria1 = "=A*"
$Criteria2 = "=*1"
$objRange.AutoFilter(7,$Criteria1,$xlAnd,$Criteria2)
# 2 Criteria with logical Or
$objRange.AutoFilter(7,$Criteria1,$xlOr,$Criteria2)
#
# TO CLEAR EXISTING FILTERS
$objRange.AutoFilter()
#

# apply autofilter and then select the active cells that are visible from top left to bottom right
$Filename = "c:\temp\mysheet.xlsx"
$objExcel = New-Object -ComObject Excel.Application
$objExcel.DisplayAlerts = $False
$objWrkBk  = $objExcel.Workbooks.Open($Filename)
$objExcel.Visible = $True

$objWrkSht1 = $objWrkBk.Worksheets.item(1)
$objWrkSht1.Select()
$objRange   = $objWrkSht1.UsedRange

$xlCellTypeLastCell = 11
$objLastcell = $objRange.SpecialCells($xlCellTypeLastCell)
$lastrow = $objLastcell.row
$lastcolumn = $objLastcell.column

$xlFilterValues = 7
$xlAnd = 1
$xlOr  = 2
# single criteria
$Criteria = "=DEF02"
$objRange.AutoFilter(7,$Criteria)

$xlLastCell = 11
#$objExcel.Cells.Select()
#$objExcel.Selection.AutoFilter(7,$Criteria)

$objExcel.ActiveCell.SpecialCells($xlLastCell).Select()
$objExcel.Range($objExcel.Selection,$objExcel.Cells(1)).Select()

After autofilter and saving as new workbook

# This is native to powershell and not really part of Excel
$mycontacts = import-csv "c:\temp\contacts.csv"
# Creates table-like custom objects from the items in a CSV file
# An array of row items, each column accessible by .ColumnName
$mycontacts[0].name,$mycontacts[0].address,$mycontacts[0].phone
'------------'
foreach ($contact in $mycontacts) { $contact.name,$contact.address,$contact.phone }

source .csv file

output

tom
1 main street
12345678
------------
tom
1 main street
12345678
dick
2 side avenue
23456781
mary
3 highland drive
34567812