Tuesday, August 12, 2008

AutoFilter using Excel VBA

AutoFilter using Excel VBA


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 Filter




See 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

11 comments:

  1. 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.

    I am using this exact string, but trying to filter the date based on today's date plus seven days.

    ReplyDelete
  2. Can you try

    dateadd("d", 7, date)

    ReplyDelete
  3. Shasur,

    How 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

    ReplyDelete
  4. Shasur,

    How 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

    ReplyDelete
  5. Anonymous1:49 PM

    Excel 2007
    My 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?

    ReplyDelete
  6. Mathew7:04 PM

    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

    ReplyDelete
  7. How do you keep the summation of Total Price after filtering?

    ReplyDelete
    Replies
    1. Collin,
      Did you receive a response to this question? I have the same question, but I have not found any answers.
      JD

      Delete
  8. Alexander asked 'What I want is to replace the Criteria1:= array with my own array"

    here'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/()...

    ReplyDelete
  9. Anonymous1:05 PM

    Simple answer is to pass an array and add an Operator to the filter:
    [CODE]
    .AutoFilter Field:=wkColumn, Criteria1:=Array( _
    param2, _
    param3, _
    param4), _
    Operator:=xlFilterValues
    [/CODE]

    ReplyDelete
  10. Anonymous9:54 AM

    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.

    Dim 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

    ReplyDelete

StumbleUpon
Share on Facebook
Related Posts Plugin for WordPress, Blogger...
Download Windows Live Toolbar and personalize your Web experience! Add custom buttons to get the information you care about most.