#1
|
|||
|
|||
How to modify code for Auto filter based on two criteria ...
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 |
#2
|
|||
|
|||
What are the contents of E1:E2 and F1:F2 ?
|
#3
|
|||
|
|||
E1=CustomerID
E2=Cust-001 ..... and so on F1=Tax% F2=2.50 or 5.00 or 9 or 18 and so on |
#4
|
|||
|
|||
Maybe try...
From: Code:
CriteriaRange:=Range("E1:E2") Code:
CriteriaRange:=Range("E1:F2") |
#5
|
|||
|
|||
Not working.
|
#6
|
|||
|
|||
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. |
#7
|
|||
|
|||
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. |
#8
|
|||
|
|||
Sample file attached.
|
#9
|
|||
|
|||
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.
|
#10
|
|||
|
|||
Okay. Thanks.
|
#11
|
|||
|
|||
Is there any way to modify the folloiwng code so that it will hide the empty cells ..
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 |
#12
|
|||
|
|||
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. |
#13
|
||||
|
||||
Quote:
Quote:
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.
__________________
Cheers, Paul Edstein [Fmr MS MVP - Word] |
#14
|
|||
|
|||
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 |