Enter Criteria Above Header To apply Autofilter

stroggo · April 16th, 2008, 12:00 AM

Hi all,

i need help with Worksheet_Change event!!!

Pl see code below:

Code:

Private Sub Worksheet_Change(ByVal Target As  Range) 

    ActiveSheet.AutoFilterMode = False 
    Range("d6:g6"). AutoFilter 
    Range("d6:g6").AutoFilter Field:=2, Criteria1:=Range("e1"), Operator:=xlAnd, _ 
    Criteria2:=Range("e2")

Cant see a facility to attach mu file.
But, will b happy to email the same if required.

What i intend to do is that : (1) if i enter a value in E3, the filter should only apply using E3 value (currently its applying E3 value but if E4 is kept blank, it takes that as = " " ) . Unfortunately, i need to have the and condition, so i have to find a way in spite of this condition. Any way out ??

(2) If i enter values in A) E3 & F3 B) E3, F3, G3...then it should make multiple filtering possible. But when i try to apply such a condition, the same problem as in point (1) occurs, it takes the and empty criteria range as = " "

Any help on this please ?
thx a lot in advance...

Shasur · April 16th, 2008, 02:40 AM

Hi

Can you use a If condition like following:

Code:

    If Len(Trim(Range("e2"))) <> 0 Then
        Range("d6:g6").AutoFilter Field:=2, Criteria1:=Range("e1"), Operator:=xlAnd, _
        Criteria2:=Range("e2")
    Else
        Range("d6:g6").AutoFilter Field:=2, Criteria1:=Range("e1")
    End If

Cheers
Shasur

http://www.dotnetdud.blogspot.com

VBA Tips & Tricks (http://www.vbadud.blogspot.com)

stroggo · April 16th, 2008, 03:35 AM

Hi,

thx a lot, this one does work !!!!

only when i remove both E1 & E2, criteria, instead of doing filtermode FAlSE, it still treats it as "equals "blank".

but yes, the basic functionality is acheived!