How to modify code for Auto filter based on two criteria ...

LearnerExcel · #1 02-11-2018, 07:19 AM

How to modify the following code for Auto filter to search based on two criteria ...
Like : CriteriaRange:=Range("E1:E2") & CriteriaRange:=Range("F1:F2")

Code:

 
PrivateSubWorksheet_Change(ByValTarget AsRange)
'Updateby Extendoffice 20160606
   IfTarget.Address = Range("E2").Address Then
       Range("A1:C20").CurrentRegion.AdvancedFilter Action:=xlFilterInPlace, CriteriaRange:=Range("E1:E2")
   EndIf
EndSub

Source: https://www.extendoffice.com/documen...ell-value.html

NoSparks · #2 02-11-2018, 12:52 PM

What are the contents of E1:E2 and F1:F2 ?

LearnerExcel · #3 02-11-2018, 01:54 PM

E1=CustomerID
E2=Cust-001 ..... and so on

F1=Tax%
F2=2.50 or 5.00 or 9 or 18 and so on

jeffreybrown · #4 02-11-2018, 02:41 PM

Maybe try...

From:

Code:

CriteriaRange:=Range("E1:E2")

To:

Code:

CriteriaRange:=Range("E1:F2")

LearnerExcel · #5 02-11-2018, 08:39 PM

Not working.

jeffreybrown · #6 02-11-2018, 09:06 PM

Not working tells me nothing.

How about attaching a small sample workbook with your setup and how it doesn't work so we can maybe get it to work.

LearnerExcel · #7 02-12-2018, 01:21 AM

Working fine now. But how can I modify formula to search with field "Code" & "Tax%" instead of "Region" & "Tax%"? The problem here is, if the field Code has 1 Character or 5 Characters it should consider it as one criteria and if it has 8 characters then it should consider it as second criteria and accordingly filter the data.

It means, if I select "1" or "5" in E3 it should show all the rows related to 1 and 5 characters in field Code. If I enter "8" in E3 then it should show rows only related to 8 characters in field Code.

LearnerExcel · #8 02-12-2018, 01:22 AM

Sample file attached.

jeffreybrown · #9 02-12-2018, 03:37 PM

IMHO, not sure why you even need to mess with advanced filter when you can just use the built in filter. Place the filter on row 5 and then on column B, filter for begins with.

LearnerExcel · #10 02-13-2018, 08:31 PM

Okay. Thanks.

LearnerExcel · #11 02-14-2018, 07:34 AM

Is there any way to modify the folloiwng code so that it will hide the empty cells with formula (as the following code does) and in additon to that it should filter data based on Cell G5 value?

Code:

 
Sub HideRowsMissingData()
Dim cl As Range, i As Integer
        
Application.ScreenUpdating = False
With Sheets("Sheet1")
    For Each cl In .Range("A4:A502")
        If cl.Value = "" Then
            .Rows(cl.Row).Hidden = True
        Else
            For i = 1 To 4
                If cl.Offset(, i).Value = "" Then
                    .Rows(cl.Row).Hidden = True
                    Exit For
                End If
            Next i
        End If
    Next cl
End With
Application.ScreenUpdating = True
End Sub

jeffreybrown · #12 02-14-2018, 04:09 PM

Sorry, but I can't modify this macro without something to work from, that is a sample worksheet. I would be guessing as to your layout and I don't want to guess. The sheet you attached in post # 8 does not have anything in column A and cell G5 says Tax%.

If this query is not related to your original post, please post a separate question.

macropod · #13 02-14-2018, 04:24 PM

Quote:

Originally Posted by jeffreybrown

If this query is not related to your original post, please post a separate question.

The OP does say:

Quote:

Originally Posted by LearnerExcel

in additon to that it should filter data based on Cell G5 value

which would seem to make the additional requirements related to the original post.

LearnerExcel: When posting questions, please supply the full details of the requirements. Revealing them piecemeal leads to frustration, particularly when code has to be re-written to accommodate previously-unspecified requirements.

LearnerExcel · #14 02-14-2018, 08:20 PM

Sorry, by mistake I posted here instead of NEW query. I will post a new one with sample data. This post is closed.

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
Sum based on multiple criteria (one of the criteria is not unique) until a threshold is reached.	phillipsdp	Excel	1	11-09-2016 12:53 AM
Filter Mail Merge based on a list of filter criteria	AusSteelMan	Mail Merge	2	05-09-2016 03:35 PM
Auto fill drops down using VBA code based on selection	AgilityJS	Word VBA	5	11-03-2015 07:50 PM
Forgotten Sort and Filter Criteria	rbdmg	Mail Merge	0	10-31-2011 09:14 PM
Modify vba code to print based on name in the InputBox	OTPM	Project	0	05-25-2011 02:03 AM