Autofilter excel with multiple criteria in multiple columns

Question

Hi All,

i need to filter an excel spreadsheet applying criteria in multiple columns, but i dont know how continue, part of my code is this:

$path = 'C:\Users\User\Desktop\List.xlsx'
$Excel = New-Object -ComObject excel.application
$Excel.visible = $True
$Excel.DisplayAlerts = $False
$wb = $excel.Workbooks.open($path)
$dtimeone = '30/12/2019'
$dtimetwo = '30/01/2021'
$ws1 = $wb.WorkSheets.item('Sheet1')
$range = $ws1.UsedRange.AutoFilter(1, '>' + $dtimeone, 1, '<' + $dtimetwo )

This apply autofilter range and it works over column 1 giving me only the rows i need, but i need to combine more filters over more columns at same time i.e.

$ws1.UsedRange.AutoFilter(2, 'WORKING', 1,'WIP'  )
$ws1.UsedRange.AutoFilter(3, 'ONTIME',1, 'WAITING' )

Could anybody help me please?

Thanks in advance!!

BR.

Answer 1

Hi,

You have to use AdvancedFilter with a criteria range if there are more than two criteria.
https://learn.microsoft.com/en-us/office/vba/api/excel.range.advancedfilter
https://support.microsoft.com/en-us/office/filter-by-using-advanced-criteria-4c9222fe-8529-4cd7-a898-3f16abdff32b

$criteria = $ws1.Range("L1","M3")  
$ws1.UsedRange.AdvancedFilter(1,$criteria)  

Best Regards，
Ian Xue

============================================

If the Answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 2

Hi Ian Xue,

i will try your suggest.

Tnahks !! BR!!.