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,

Glad the first filter works fine!

1) This might happen if your Summary sections starts immediately one row below your Table; try to move it a little bit below, e.g. starting from row 76.
2) Yes, you just need to add "visibledropdown:=False" parameter to your code; see below:

    With rngTable
        Call .AutoFilter(Field:=1, Criteria1:=">0", [B]visibledropdown:=False[/B])
        Call .AutoFilter(Field:=2, Criteria1:="*" & Range("B10").Value & "*", [B]visibledropdown:=False[/B])
        Call .AutoFilter(Field:=3, Criteria1:="*" & Range("C10").Value & "*", [B]visibledropdown:=False[/B])
        Call .AutoFilter(Field:=4, Criteria1:="*" & Range("D10").Value & "*", [B]visibledropdown:=False[/B])
    End With

 

[hrayani]

Hi JustynaMK,

Now all is fine except one thing .....

See there are four criteria on which the code is working. Is it possible for the code to apply first filter on the entire range i.e. till 102 and the rest three criteria till row # 74... I don't want the Summary part to be filtered fully.
I mean all the values which are greater then zero "0" should only be filtered.

Hope i am not getting on your head now...

 

[JustynaMK]

Hi Humayun,

For such custom scenario, I wouldn't even use AutoFilter - please check another solution below:

Sub yearly_report_show()
    Dim lngRow              As Long
    Dim blnA                As Boolean
    Dim blnB                As Boolean
    Dim blnC                As Boolean
    Dim blnD                As Boolean
    
    Application.ScreenUpdating = False
    
    With ActiveSheet
        Call .Unprotect(Password:="merchant")
        .Rows("1:4").EntireRow.Hidden = True
        .Rows("10:102").EntireRow.Hidden = False
[COLOR=#ff0000]        For lngRow = 11 To 102[/COLOR]
[COLOR=#ff0000]            If lngRow <= 74 Then[/COLOR]
[COLOR=#ff0000]                blnA = (.Range("A" & lngRow).Value > 0)[/COLOR]
[COLOR=#ff0000]                blnB = InStr(.Range("B" & lngRow).Value, .Range("B10").Value)[/COLOR]
[COLOR=#ff0000]                blnC = InStr(.Range("C" & lngRow).Value, .Range("C10").Value)[/COLOR]
[COLOR=#ff0000]                blnD = InStr(.Range("D" & lngRow).Value, .Range("D10").Value)[/COLOR]
[COLOR=#ff0000]                .Rows(lngRow).Hidden = ((blnA + blnB + blnC + blnD) > -4)[/COLOR]
[COLOR=#0000ff]            Else[/COLOR]
[COLOR=#0000ff]                blnA = (.Range("A" & lngRow).Value <= 0)[/COLOR]
[COLOR=#0000ff]                .Rows(lngRow).Hidden = blnA[/COLOR]
            End If
        Next lngRow
        Call .Protect(DrawingObjects:=True, Contents:=True, Scenarios:=True, _
                      AllowFormattingColumns:=True, AllowFormattingRows:=True, Password:="merchant")
    End With
End Sub

Red section:
For each row between 11 and 74, check if: column A is greater than 0, column B contains string B10, column C contains string C10, column D contains D10. If all 4 conditions are met, then do not hide this row.

Blue section:
For each row between 75 and 102, check if: column A is greater than 0. If this condition is met, then do not hide this row.

 

[hrayani]

Hi JustynaMK,

First of all bunch of thanks for your time.... I always wonder how you guys do this coding / programming.
I wish i could do the same.... but Ahhhh....

Tried the code and found that the rows in Column B,C & D are only filtering if exact strings are found in Cells B10, C10 & D10.
Is it possible to set the code to filter rows if any part of the string is matched - i mean it does not have to be an exact match.
Like what we do in custom filter (contains part).

Regards,

Humayun

 

[hrayani]

Hi JustynaMK,

sorry dear.... Its working 100% Perfect...

I don,t know why it did not work at the first go..... I am sure i must have gone wrong somewhere.
Its working now 100%..


EDIT POST

it stopped again.... same issue what i mentioned in Post # 14

What i found is its only looking at the figures when it comes to the contains criteria
Like i have Humayun 1979... So when i type "Humayun" than its not working.
but asa i type "1979" than its filtering records.

 

[hrayani]

Update:

Its looking at everything - figures / text....

Its just case sensitive nothing else... Otherwise uptill now working 100%

Can we set the code not to be case sensitive... Like "Humayun" & "humayun" should be treated same

 

[JustynaMK]

Hi Humayun,

Cool news! Yes, InStr function is case sensitive. In order to turn it into non-case sensitive function, you need to replace blnB, blnC and blnD with the following lines:

                blnB = InStr(Ucase(.Range("B" & lngRow).Value), Ucase(.Range("B10").Value))
                blnC = InStr(Ucase(.Range("C" & lngRow).Value), Ucase(.Range("C10").Value))
                blnD = InStr(Ucase(.Range("D" & lngRow).Value), Ucase(.Range("D10").Value))

 

[hrayani]

Hi JustynaMK,

Just started working on it.. and its working PERFECT

I really appreciate. Thanks a lot

Regards,

Humayun

 

[JustynaMK]

Awesome! Take care.

 

[hrayani]

Hi JustynaMK,

I am running the code and its working perfect..

I tried to copy the code and amended it a bit for another sheet. but somehow its not working.

Filter Data from Row 4:1000
Columns Data A:V

Here is the amended code:

 Sub test()  
 Dim lngRow              As Long
    Dim blnA                As Boolean
    Dim blnB                As Boolean
    Dim blnC                As Boolean
    Dim blnD                As Boolean
    Dim blnE                As Boolean
    Dim blnF                As Boolean
    Dim blnG                As Boolean
    Dim blnH                As Boolean
    Dim blnI                As Boolean
    Dim blnJ                As Boolean
    Dim blnK                As Boolean
    Dim blnL                As Boolean
    Dim blnM                As Boolean
    Dim blnN                As Boolean
    Dim blnO                As Boolean
    Dim blnP                As Boolean
    Dim blnQ                As Boolean
    Dim blnR                As Boolean
    Dim blnS                As Boolean
    Dim blnT                As Boolean
    Dim blnU                As Boolean
    Dim blnV                As Boolean
    
    Application.ScreenUpdating = False
    
    With ActiveSheet
        
        For lngRow = 4 To 1000
            If lngRow <= 1000 Then
                blnA = InStr(UCase(.Range("A" & lngRow).Value), UCase(.Range("A2").Value))
                blnB = InStr(UCase(.Range("B" & lngRow).Value), UCase(.Range("B2").Value))
                blnC = InStr(UCase(.Range("C" & lngRow).Value), UCase(.Range("C2").Value))
                blnD = InStr(UCase(.Range("D" & lngRow).Value), UCase(.Range("D2").Value))
                blnE = InStr(UCase(.Range("E" & lngRow).Value), UCase(.Range("E2").Value))
                blnF = InStr(UCase(.Range("F" & lngRow).Value), UCase(.Range("F2").Value))
                blnG = InStr(UCase(.Range("G" & lngRow).Value), UCase(.Range("G2").Value))
                blnH = InStr(UCase(.Range("H" & lngRow).Value), UCase(.Range("H2").Value))
                blnI = InStr(UCase(.Range("I" & lngRow).Value), UCase(.Range("I2").Value))
                blnJ = InStr(UCase(.Range("J" & lngRow).Value), UCase(.Range("J2").Value))
                blnK = InStr(UCase(.Range("K" & lngRow).Value), UCase(.Range("K2").Value))
                blnL = InStr(UCase(.Range("L" & lngRow).Value), UCase(.Range("L2").Value))
                blnM = InStr(UCase(.Range("M" & lngRow).Value), UCase(.Range("M2").Value))
                blnN = InStr(UCase(.Range("N" & lngRow).Value), UCase(.Range("N2").Value))
                blnO = InStr(UCase(.Range("O" & lngRow).Value), UCase(.Range("O2").Value))
                blnP = InStr(UCase(.Range("P" & lngRow).Value), UCase(.Range("P2").Value))
                blnQ = InStr(UCase(.Range("Q" & lngRow).Value), UCase(.Range("Q2").Value))
                blnR = InStr(UCase(.Range("R" & lngRow).Value), UCase(.Range("R2").Value))
                blnS = InStr(UCase(.Range("S" & lngRow).Value), UCase(.Range("S2").Value))
                blnT = InStr(UCase(.Range("T" & lngRow).Value), UCase(.Range("T2").Value))
                blnU = InStr(UCase(.Range("U" & lngRow).Value), UCase(.Range("U2").Value))
                blnV = InStr(UCase(.Range("V" & lngRow).Value), UCase(.Range("V2").Value))
                .Rows(lngRow).Hidden = ((blnA + blnB + blnC + blnD + blnE + blnF + blnG + blnH + blnI + blnJ + blnK + blnL + blnM + blnN + blnO + blnP + blnQ + blnR + blnS + blnT + blnU + blnV) > -4)
            
            End If
        Next lngRow
        
    End With
End Sub

Can u please have a loot at it and let me know if i am doing something wrong.
I got it worked for 4 to 5 Columns but i am unable to get it to work when the Columns Increases

Regards,

Humayun