PDA

View Full Version : Autofilter optimization



wl_tinus
05-30-2006, 06:34 AM
For the persons with no or almost no excel knowledge in my office I want to make it easier to use autofilter.

To do this I'm trying to write a VBA code that takes the input from several fields and uses those variables in the autofilter fields. That part was easy to figure out.

It becomes difficult when you leave a field empty. Autfilter will give no results in stead of all results.

The database consists of 17 columns. I the 'menu' I'm building I want to give the user the option to filter on about five of those 17 columns (Example: Type, Size1, Size2, Age, Mileage). If one of those fields is left empty I want the autofilter to show alle data. (I'm won't be able to post the database, as I would most likely lose my job due to privacy issues)

I have this working code for two fields, but I was wondering if there is any smarter way to do this. The code becomes quite large and difficult for 5 or more criteria.

Private Sub CommandButton1_Click()
Dim CritDis As String
Dim CritGew As String
If Cells(3, 2) <> "" And Cells(2, 2) <> "" Then
CritDis = Cells(3, 2)
CritGew = Cells(2, 2)
Sheets("DATA").Select
Selection.AutoFilter Field:=16, Criteria1:=CritDis
Selection.AutoFilter Field:=10, Criteria1:=CritGew
ElseIf Cells(2, 2) <> "" And Cells(3, 2) = Empty Then
CritGew = Cells(2, 2)
Sheets("DATA").Select
Selection.AutoFilter Field:=10, Criteria1:=CritGew
ElseIf Cells(3, 2) <> "" And Cells(2, 2) = Empty Then
CritDis = Cells(3, 2)
Sheets("DATA").Select
Selection.AutoFilter Field:=16, Criteria1:=CritDis
Else
Sheets("DATA").Select
ActiveSheet.ShowAllData
End If
End Sub

If anyone could help me, I would really appreciate it.

Greetz,

Martijn

Norie
05-30-2006, 07:47 AM
How are you actually getting user input?

wl_tinus
05-30-2006, 07:56 AM
The user puts the input in a cell.

As you can see from the code I assing the text in a cell to a variable

Norie
05-30-2006, 08:09 AM
So why not just add a test to see if there's anything in the cell?

wl_tinus
05-30-2006, 01:37 PM
I'm not sure that I understand.

Didn't I do this with the following part of my code?


If Cells(3, 2) <> "" And Cells(2, 2) <> "" Then


The thing is that if I want to check for five criteria the code will become quite lenghty and prone for errors.


If Cells(2, 2) <> "" And Cells(3, 2) <> "" If Cells(4, 2) <> "" And Cells(5, 2) <> "" If Cells(6, 2) <> "" Then


I read my post again, and probably was a bit unclear.

In my document I want to give the user the option to filter on five criteria. If one or more criteria is left blank, I want the autofilter to discard that criteria. If I only enter criteria for age (lets say >5) and left the others blank, the autofilter would give no result if I wouldnt use the code above. Wat I am trying is that the autofilter only uses the age criteria and not the ones that are left blank.

I know I could use advanced filter to make this work, but autofilter has a few good options for thos who know how to work it.