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
Posts
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?
ReplyDelete