How do I set up Auto Filters in Excel?

#How do I set up Auto Filters in Excel?

#This function sets up a spreadsheet and then sets Auto filters
Someone asked how to select both Jack and Elizebeth.
$xl.Selection.AutoFilter 2, Jack, $xlAnd, Elizabeth #Jack & Elizabeth items only

Thanks to an anonymous reader, we can now do a more than two.

$xlFilterValues = 7 # found in MS documentation
$filterList = “Jack”,”Elizabeth” # array
$xl.Selection.AutoFilter(2, $filterList ,$xlFilterValues)

###### Start Posh Script ########
 Function XLAutoFilter { 
 $xlTop10Items = 3
 $xlTop10Percent = 5
 $xlBottom10Percent = 6
 $xlBottom10Items = 4
 $xlAnd = 1
 $xlOr = 2
 $xlNormal = -4143
 $xlPasteValues = -4163 # Values only, not formulas 
 $xlCellTypeLastCell = 11 # to find last used cell
 $xl = New-Object #NAME? Excel.Application 
 $xl.Visible = $true
 $xl.DisplayAlerts = $False
 $wb = $xl.Workbooks.Add()
 $ws = $wb.Worksheets.Item(1)
 $ws = $wb.Sheets.Add()
 $ws = $wb.Sheets.Add()
 $ws1 = $wb.worksheets | where {$_.name #NAME? Sheet1} #<------- Selects sheet 1
 $ws2 = $wb.worksheets | where {$_.name #NAME? Sheet2} #<------- Selects sheet 2
 $ws3 = $wb.worksheets | where {$_.name #NAME? Sheet3} #<------- Selects sheet 3
 $ws4 = $wb.worksheets | where {$_.name #NAME? Sheet4} #<------- Selects sheet 4
 $ws5 = $wb.worksheets | where {$_.name #NAME? Sheet5} #<------- Selects sheet 5
 $ws1.Tab.ColorIndex = 8
 $ws2.Tab.ColorIndex = 7
 $ws3.Tab.ColorIndex = 6
 $ws4.Tab.ColorIndex = 5
 $ws5.Tab.ColorIndex = 4
 $ws1.name = Detail
 $ws2.name = JackOnly
 $ws3.name = Top2
 $ws4.name = LowestHighest
 $ws5.name = Top25Percent
 $ws1.cells.Item(1,1) = Amount
 $ws1.cells.Item(1,2) = SalesPerson
 $ws1.cells.Item(2,1) = 1
 $ws1.cells.Item(2,2) = Jack
 $ws1.cells.Item(3,1) = 2
 $ws1.cells.Item(3,2) = Elizabeth
 $ws1.cells.Item(4,1) = 3
 $ws1.cells.Item(4,2) = Renee
 $ws1.cells.Item(5,1) = 4
 $ws1.cells.Item(5,2) = Elizabeth
 $ws1.cells.Item(6,1) = 5
 $ws1.cells.Item(6,2) = Jack
 $ws1.cells.Item(7,1) = 6
 $ws1.cells.Item(7,2) = Renee
 $ws1.cells.Item(8,1) = 7
 $ws1.cells.Item(8,2) = Elizabeth
 $ws1.cells.Item(9,1) = 8
 $ws1.cells.Item(9,2) = Jack
 $ws1.cells.Item(10,1) = 9
 $ws1.cells.Item(10,2) = Renee
 $ws1.cells.Item(11,1) = 10
 $ws1.cells.Item(11,2) = Jack
 $ws1.cells.Item(12,1) = 11
 $ws1.cells.Item(12,2) = Jack
 $ws1.cells.Item(13,1) = 12
 $ws1.cells.Item(13,2) = Elizabeth
 $ws1.cells.Item(14,1) = 13
 $ws1.cells.Item(14,2) = Renee
 $ws1.cells.Item(15,1) = 14
 $ws1.cells.Item(15,2) = Elizabeth
 $ws1.cells.Item(16,1) = 15
 $ws1.cells.Item(16,2) = Jack
 $ws1.cells.Item(17,1) = 16
 $ws1.cells.Item(17,2) = Renee
 $ws1.cells.Item(18,1) = 17
 $ws1.cells.Item(18,2) = Elizabeth
 $ws1.cells.Item(19,1) = 18
 $ws1.cells.Item(19,2) = Jack
 $ws1.cells.Item(20,1) = 19
 $ws1.cells.Item(20,2) = Renee
 $ws1.cells.Item(21,1) = 20
 $ws1.cells.Item(21,2) = Renee
 $used = $ws1.usedRange
 $lastCell = $used.SpecialCells($xlCellTypeLastCell)
 $lastrow = $lastCell.row
 $r = $ws1.Range("A1:B$lastrow")
 $ws1.Range("A1:B$lastrow").Copy()  
 $ws2.Select() | Out-Null
 [void]$ws2.Range("A1").PasteSpecial(-4163)  
 $ws3.Select() | Out-Null
 [void]$ws3.Range("A1").PasteSpecial(-4163)  
 $ws4.Select() | Out-Null
 [void]$ws4.Range("A1").PasteSpecial(-4163)  
 $ws5.Select() | Out-Null
 [void]$ws5.Range("A1").PasteSpecial(-4163)  
 #  
 $ws5.Range("A1").Select()  
 # AutoFilter structure - Field, Criteria, Operator  
 #$xl.Selection.AutoFilter 1, 10, $xlTop10Items #top 10
 $xl.Range("A1").Select() | Out-Null
 $xl.Selection.AutoFilter(1, 2, $xlTop10Items) #top 2 
 #$xl.Selection.AutoFilter 1, 10, $xlTop10Percent #top 10 percent  
 #$xl.Selection.AutoFilter 1, 25, $$xlTop10Percent #top 25 percent  
 #$xl.Selection.AutoFilter 1, 5, $xlBottom10Items #Lowest 5 Items  
 #$xl.Selection.AutoFilter 1, 10, $$xlBottom10Percent #Bottom 10 percent  
 #$xl.Selection.AutoFilter 1, >15 #size greater 15
 #$xl.Selection.AutoFilter 1, >19,XLOr , <2 #Lowest and Highest 
 #$xl.Selection.AutoFilter 2, Jack #Jack items only
 $ws5.cells.Item.EntireColumn.AutoFit  
 $ws2.Select()  
 $ws2.Range("A1").Select()  
 # AutoFilter structure - Field, Criteria, Operator  
 #$xl.Selection.AutoFilter 1, 10, $xlTop10Items #top 10
 #$xl.Selection.AutoFilter 1, 2, $xlTop10Items #top 2
 #$xl.Selection.AutoFilter 1, 10, $xlTop10Percent #top 10 percent  
 #$xl.Selection.AutoFilter 1, 25, $xlTop10Percent #top 25 percent  
 #$xl.Selection.AutoFilter 1, 5, $xlBottom10Items #Lowest 5 Items  
 #$xl.Selection.AutoFilter 1, 10, $xlBottom10Percent #Bottom 10 percent  
 #$xl.Selection.AutoFilter 1, >15 #size greater 15
 #$xl.Selection.AutoFilter 1, >19,XLOr , <2 #Lowest and Highest 
 $xl.Selection.AutoFilter(2, Jack) #Jack items only 
 $ws2.cells.Item.EntireColumn.AutoFit  
 $ws4.Select()  
 $ws4.Range("A1").Select()  
 # AutoFilter structure - Field, Criteria, Operator  
 #$xl.Selection.AutoFilter 1, 10, $xlTop10Items #top 10
 #$xl.Selection.AutoFilter 1, 2, $xlTop10Items #top 2
 #$xl.Selection.AutoFilter 1, 10, $xlTop10Percent #top 10 percent  
 #$xl.Selection.AutoFilter 1, 25, $xlTop10Percent #top 25 percent  
 #$xl.Selection.AutoFilter 1, 5, $xlBottom10Items #Lowest 5 Items  
 #$xl.Selection.AutoFilter 1, 10, $xlBottom10Percent #Bottom 10 percent  
 #$xl.Selection.AutoFilter 1, >15 #size greater 15
 $xl.Selection.AutoFilter(1, >19,$xlOr , <2) #Lowest and Highest  
 #$xl.Selection.AutoFilter 2, Jack #Jack items only
 $ws4.cells.Item.EntireColumn.AutoFit  
 # Top25Percent 
 $ws5.Select()  
 $ws5.Range("A1").Select()  
 # AutoFilter structure - Field, Criteria, Operator  
 #$xl.Selection.AutoFilter 1, 10, $xlTop10Items #top 10
 #$xl.Selection.AutoFilter 1, 2, $xlTop10Items #top 2
 #$xl.Selection.AutoFilter 1, 10, $xlTop10Percent #top 10 percent  
 $xl.Range("A1").Select() | Out-Null
 $xl.Selection.AutoFilter(1,"25",$xlTop10Percent) #top 25 percent  
 #$xl.Selection.AutoFilter 1, 5, $xlBottom10Items #Lowest 5 Items  
 #$xl.Selection.AutoFilter 1, 10, $xlBottom10Percent #Bottom 10 percent  
 #$xl.Selection.AutoFilter 1, >15 #size greater 15
 #$xl.Selection.AutoFilter 1, >19,XLOr , <2 #Lowest and Highest 
 #$xl.Selection.AutoFilter 2, Jack, $xlAnd, Elizabeth #Jack & Elizabeth items only
 $ws5.cells.Item.EntireColumn.AutoFit  
 } # End Function
############# End POSH Script ###############

Advertisements

2 thoughts on “How do I set up Auto Filters in Excel?

    • Looks like you found an answer:

      $xlCellTypeVisible = 12
      $cells = $ws.UsedRange.Columns.Item(1).SpecialCells($xlCellTypeVisible) |
      Select-Object -Skip 1
      if ($cells) {
      foreach ($cell in $cells) {
      $cell.Row
      }
      }

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s