Power Platform Community Forum Thread Details

Hello,

I need help creating a VBScript within Power Automate Desktop. I need to filter 2 values (DR and CR) within 1 column. However, within PAD, you can only set 1 variable at a time. Is there a way to filter both the DR and CR values in one column using VBScript?

This is what I have so far, but am stuck on how to add DR to the current filter along with CR.

Set xlApp=Createobject("Excel.Application")
Set xlWbk=xlApp.Workbooks.Open("%FilePath%")
xlApp.Visible=true
Set xlWksht = xlWbk.ActiveSheet
Set xlWksht = xlWbk.Worksheets(5)
xlWksht.Range("D1").AutoFilter %FilterColumn%, "%FilterCR%"
xlWksht.Range("A:S").Copy
Set xlWksht = xlWbk.Worksheets(6)
xlWksht.Range("A1").PasteSpecial
Set xlWksht = xlWbk.Worksheets(5)
xlWksht.Range("D1").AutoFilter %FilterColumn%, "%FilterSLS%"
xlWksht.Range("A:S").Copy
Set xlWksht = xlWbk.Worksheets(8)
xlWksht.Range("A1").PasteSpecial
Set xlWksht = xlWbk.Worksheets(5)
xlWksht.Range("D1").AutoFilter %FilterColumn%, "%FilterPMT%"
xlWksht.Range("A:V").Copy
Set xlWksht = xlWbk.Worksheets(7)
xlWksht.Range("A1").PasteSpecial

Categories:

Power Automate Desktop

Not sure where you want to do that, as I can see you filtering the same column 3 times for different values, but I assume it's this line:

xlWksht.Range("D1").AutoFilter %FilterColumn%, "%FilterCR%"

In order to apply several filter values, you need to pass in an Array instead of a single value, and use the XlAutoFilterOperator set to 7. So, modify this line to:

xlWksht.Range("D1").AutoFilter %FilterColumn%, Array("%FilterCR%","%FilterDR%"), 7

This is really not much of a PAD question, and simply a VBScript question.

See here for reference on the Range.AutoFilter method in VBA (it's slightly different in VBScript, as you do not need to use named properties, but you can get the general idea of what I did there): https://learn.microsoft.com/en-us/office/vba/api/excel.range.autofilter

Also this for reference on what the XlAutoFilterOperator settings mean: https://learn.microsoft.com/en-us/office/vba/api/excel.xlautofilteroperator