VBA Auto Filter

[hrayani]

Hello Friends,

I am using a VBA code to filter record. Here is the code

Sub aaa()

ActiveSheet.Range("A1:B25").AutoFilter Field:=1, Criteria1:=Range("C1"), _
Operator:=xlOr

ActiveSheet.Range("A1:B25").AutoFilter Field:=2, Criteria1:="*a*", _
Operator:=xlAnd

End Sub

I would like to give a range (D1) in the Field:=2 part of the code instead of "a"

Any help will be appreciated

Regards,

Humayun

 

[JustynaMK]

Hi Humayun,

Try the following code and let me know if that works for you:

Sub aaa()
    With ActiveSheet.Range("A1:B25")
        .AutoFilter Field:=1, Criteria1:=Range("C1").Value, Operator:=xlOr
        .AutoFilter Field:=2, Criteria1:="*" & Range("D1").Value & "*", Operator:=xlAnd
    End With
End Sub

 

[hrayani]

Thanks Justyna for the reply,

I searched the web for my solution and found one code which i amended as per my need.

here it is

Sub supplier_filter_record()Application.ScreenUpdating = False


If ActiveSheet.AutoFilterMode Then Cells.AutoFilter


Dim strName As String


strName1 = InputBox("Please Enter ARTICLE")
strName2 = InputBox("Please Enter QUALITY?")
strName3 = InputBox("Please Enter UNIT?")


ActiveSheet.Range("A10:D73").AutoFilter Field:=1, Criteria1:=">0"
ActiveSheet.Range("A10:D73").AutoFilter Field:=2, Criteria1:="=*" & strName1 & "*"
ActiveSheet.Range("A10:D73").AutoFilter Field:=3, Criteria1:="=*" & strName2 & "*"
ActiveSheet.Range("A10:D73").AutoFilter Field:=4, Criteria1:="=*" & strName3 & "*"


End Sub

Sure i will try the code and will let u know if it worked for me.

Thanks

 

[JustynaMK]

OK! Fingers crossed it works. Take care.

 

[hrayani]

Hi JustynaMK,

I just tried the code you provided.... and its much more user friendly than the one i am working on right now...

Sub aaa()
    With ActiveSheet.Range("A1:B25")
        .AutoFilter Field:=1, Criteria1:=Range("C1").Value, Operator:=xlOr
        .AutoFilter Field:=2, Criteria1:="*" & Range("D1").Value & "*", Operator:=xlAnd
    End With
End Sub

can u please ammend the code as per below criterias

Total Range to be filtered is A10:D102

There are four criteria which i need

1) Column A = Any value which is greater than zero in A10:A102 should be shown... rest all should be hidden
2) Column B = Range B10:B102 to look text that contains in Cell B10.... Clear Filters from Column B if B10 is empty
3) Column C = Range C10:C102 to look text that contains in Cell C10.... Clear Filters from Column C if C10 is empty
4) Column D = Range D10:D102 to look text that contains in Cell D10.... Clear Filters from Column D if D10 is empty


Regards,

Humayun

 

[JustynaMK]

Hi Humayun,

How about the following code:

Sub aaa()
    Dim rngTable        As Excel.Range


    Set rngTable = ActiveSheet.Range("A10:D102")
    With rngTable
        Call .AutoFilter(Field:=1, Criteria1:=">=0")
        Call .AutoFilter(Field:=2, Criteria1:="*" & Range("B10").Value & "*")
        Call .AutoFilter(Field:=3, Criteria1:="*" & Range("C10").Value & "*")
        Call .AutoFilter(Field:=4, Criteria1:="*" & Range("D10").Value & "*")
    End With
End Sub

If any of B10, C10, D10 is empty, then macro will filter on "*" meaning that it will select all values from that column (i.e. filter won't change your results).

Does it help?

 

[hrayani]

Thanks JustynaMK

The code is working buth with the following issue

When i entered a value in C10 (its "flannel" in my case) it is filtering all the records with flannel but its not looking at Column A
where the values are greater then zero... I mean it should always show values which are greater then "0" in column A.

 

[JustynaMK]

Sorry, my mistake. The criteria I wrote for the first Filter shows values which are greater than AND equal to zero.
Please change the first "Call" line to:

Call .AutoFilter(Field:=1, Criteria1:=">0")

 

[hrayani]

Oh...... I should have looked into that myself.....

Thanks.... let me try this... Will update you shortly

 

[hrayani]

Thanks JustynaMK,

Working Just Perfect Now

Here is what i am using a mixture of your and mine code

Sub yearly_report_show()    
    Application.ScreenUpdating = False
    ActiveSheet.Unprotect Password:="merchant"
    If ActiveSheet.AutoFilterMode Then Cells.AutoFilter
    Rows("1:4").EntireRow.Hidden = True
    
[B]    Dim rngTable        As Excel.Range[/B]


[B]    Set rngTable = ActiveSheet.Range("A10:D74")[/B]
[B]    With rngTable[/B]
[B]        Call .AutoFilter(Field:=1, Criteria1:=">0")[/B]
[B]        Call .AutoFilter(Field:=2, Criteria1:="*" & Range("B10").Value & "*")[/B]
[B]        Call .AutoFilter(Field:=3, Criteria1:="*" & Range("C10").Value & "*")[/B]
[B]        Call .AutoFilter(Field:=4, Criteria1:="*" & Range("D10").Value & "*")[/B]
        
                ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True _
        , AllowFormattingColumns:=True, AllowFormattingRows:=True, Password:="merchant"
       End With
End Sub

Now the only problem remains is you can see in the code that i have changed the range from D102 to D74.
The reason behind this is that i do have a Summary below row # 74.. But when the code starts filtering the record it does filter
all the data which is below row # 74 too.

1) Can we tell the code to just filter the record till Row # 74. and let all the other rows as they are
2) Is it possible to hide the Auto Filter Arrows

Regards,

Humayun