Filters are one of the most used Excel utilities. Here is a simple way to create a filter through code
Sub Simple_AutoFilter()
Dim oWS As Worksheet
On Error GoTo Err_Filter
oWS = ActiveSheet
oWS.UsedRange.AutoFilter Field:=2, Criteria1:="Apple"
Finally:
If Not oWS Is Nothing Then oWS = Nothing
Err_Filter:
If Err <> 0 Then
MsgBox(Err.Description)
Err.Clear
GoTo Finally
End If
End Sub
Range Before Filtering
Range after FilterSee also:
Create AutoFilter with Multiple Criteria using Excel VBA
AutoFilter using Excel VBA
Check for existence of Filter using Excel VBA
Excel Filter Show All using VBA
Retrieve / Get First Row of Excel AutoFilter using VBA
Good post, but how do you add VB code against a calculated field and have that field returned back into the filter and have the logic not see it as a text string.
ReplyDeleteI am using this exact string, but trying to filter the date based on today's date plus seven days.
Can you try
ReplyDeletedateadd("d", 7, date)
Shasur,
ReplyDeleteHow do I feed autofilter an array of criteria. in your example you only use 2 criteria and I need to use more mucht more.
what I have now is ..
For Each ws In ActiveWorkbook.Sheets
If IsNumeric(ws.Name) Then
ws.Activate
Set CurrentFilterRange = ws.UsedRange
'ws.AutoFilterMode = False
For i = 0 To UBound(FilterList())
CurrentFilterRange.AutoFilter Field:=1, Criteria1:=FilterList(i)
Next i
End If
Next ws
Shasur,
ReplyDeleteHow do I use an array of criteria using autofilter.
recording a macro tells me the following :
ActiveSheet.UsedRange.AutoFilter Field:=1, Criteria1:=Array("Criteria1", "Criteria2", "Criteria3"), Operator:=xlFilterValues
What I want is to replace the Criteria1:= array with my own array
so What I have made is this but only the last entry will be visible (obviously) how can I feed the array into the autofilter ?
For Each ws In ActiveWorkbook.Sheets
If IsNumeric(ws.Name) Then
ws.Activate
Set CurrentFilterRange = ws.UsedRange
'ws.AutoFilterMode = False
For i = 0 To UBound(FilterList())
CurrentFilterRange.AutoFilter Field:=1, Criteria1:=FilterList(i)
Next i
End If
Next ws
many thanks
Alexander
Excel 2007
ReplyDeleteMy CustomerName has business rules for the sort order.
For Access queries, forms, listboxes, I created a nice custom vba Function that works great.
In Excel 2007, I use copyfromrecordset. So the customer name custom order is right the first time.
Once users sort by column 2, 3 then want to re-sort CustomerName again.
CustomerName sorted by A-Z is not correct.
Is there a way to add my custom function to Excel (in the modules) and have it show up in the AutoFilter list choice?
Not sure if you can add a custom function in AutFilter, but you can add the function to a Menu that will sort. Or you can call a subroutine when the text is filtered
ReplyDeleteHow do you keep the summation of Total Price after filtering?
ReplyDeleteCollin,
DeleteDid you receive a response to this question? I have the same question, but I have not found any answers.
JD
Alexander asked 'What I want is to replace the Criteria1:= array with my own array"
ReplyDeletehere's my suggestion...and I am just now starting to work this into one of the reports I support:
- Set up the worksheet with the filter as you believe it will be set.
- Create the following in VBE behind that worksheet
sub stuff()
Dim w as worksheet
Set w = Thisworkbook.worksheets(1)'or whatever the sheet index is
End Sub
- Make the execution stop at "End Sub"
- Create a watch for "w"
Now for the fun...Drill down "w" to "Autofilter" to "Filters"
Once at Filters, open each of the Items (each Item is a filter/column)...and look at the entries in Criteria1 and Criteria2. Each will have either a formula for the filter, a code, or break down into an array of formulas.
- Make your code produce a String like what you see in Criteria1/2/()...
Simple answer is to pass an array and add an Operator to the filter:
ReplyDelete[CODE]
.AutoFilter Field:=wkColumn, Criteria1:=Array( _
param2, _
param3, _
param4), _
Operator:=xlFilterValues
[/CODE]
Creating an autofilter array from a range. Example is a range from column C -> 4 rows including the current row. Goes to another sheet and uses the values from the range as the criteria.
ReplyDeleteDim myarray(10)
i = ActiveCell.Row
For j = 0 To 3
myarray(j) = Range("C" & i + j).Value
Next j
Sheets("data").Select
ActiveSheet.Range("$A$3:$AI$3567").AutoFilter Field:=9, Criteria1:=myarray, Operator:=xlFilterValues