MS Office Forum

Ask Question   UnAnswered
Home » Forum » MS Office       RSS Feeds

Filter using VBA

  Asked By: Jordon    Date: Sep 21    Category: MS Office    Views: 2067

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.



4 Answers Found

Answer #1    Answered By: Cleopatra Massri     Answered On: Sep 21

I'd dig around in http://j-walk.com/ss/excel/tips/index.htm to see if John
has anything you can use.

Answer #2    Answered By: Muntasir Bashara     Answered On: Sep 21

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 #3    Answered By: Cadencia Bernard     Answered On: Sep 21

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 #4    Answered By: Patty Freeman     Answered On: Sep 21

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.