MS Office Forum

Ask Question   UnAnswered
Home » Forum » MS Office       RSS Feeds

Filter using VBA

  Asked By: Adelisa    Date: Dec 24    Category: MS Office    Views: 2045

Does anyone has a how-to or knows any website that explains how to filter cells
using VBA? I just know how to filter by the Excel Interface, using Auto-Filter
or Advanced Filter, but I need to do that with code cause I have to work with an
user input.



3 Answers Found

Answer #1    Answered By: Muhammad Evans     Answered On: Dec 24

You can control autofilters from VBA. For instance, these two lines from
one of my spreadsheets:

Call Selection.AutoFilter(Field:=4, Criteria1:="<=30-Jun-2006")
Call Selection.AutoFilter(Field:=10, Criteria1:="=")

These set a date filter  on column 4 and an "empty" filter on column 10.

The following clears any filters (i.e. "show all")

With ActiveSheet
If .FilterMode Then
Call .ShowAllData
End If
End With

Note that I don't explicitly define a Selection for the AutoFilter - as long
as the correct sheet is current it seems to work  OK. AutoFilter needs to
have been associated with the relevant columns first, of course.

Answer #2    Answered By: Kian Evans     Answered On: Dec 24

for multi filtering u may use this code  examples...

Selection.AutoFilter Field:=1, Criteria1:="1"

If ComboBox2.Value <> "" Then
Selection.AutoFilter Field:=3, Criteria1:="=" & a4
Sheets("n").Range("a1").Value = 1
End If

If ComboBox3.Value <> "" Then
Selection.AutoFilter Field:=4, Criteria1:="=" & a3
Sheets("n").Range("a1").Value = 1
End If

If ComboBox1.Value <> "" Then
Selection.AutoFilter Field:=5, Criteria1:="=" & a2
Sheets("n").Range("a1").Value = 1
End If

If DTPicker1.Value = "" Then
Sheets("n").Range("b2").Value = InputBox("Please input  date
Sheets("n").Range("b2").Value = DTPicker1.Value
Selection.AutoFilter Field:=15, Criteria1:=">=" & a1,
operator:=xlAnd, Criteria2:="<=" & b1
End If

This code for a productivity manegement desicion support system...
it may became a specific example but u may use that format what ever
u want ( first paragraph for refreshing all input filtering history
, and other for multi criteria, and last of them is for filtering
date interval)

Answer #3    Answered By: Tomas Thompson     Answered On: Dec 24

We can make use of this site.It has downloadable examples.

Didn't find what you were looking for? Find more on Filter using VBA Or get search suggestion and latest updates.