Home |
Search |
Today's Posts |
#1
![]()
Posted to microsoft.public.excel.programming
|
|||
|
|||
![]()
I have set up a code to do the following:
When the dropdown control in the spreadsheet changed 1. it will clear range F5:G20, 2. if user select "(all)", then it will perform advance filter on column V (unique value only) and paste it to range G5:g20. However, if user select any other value, it will perform advance filter on colum U & V with critieria range F1:F2 and paste it at F5:G20 (I want to keep the data set) 3. Sort the result in order I'm not sure why, but when I run the code, it will not preform the advance filter task and also, it'll clear my data set in column U & V, could someone please check my code below and give me an advice on how to correct it? Private Sub DropDown83_Change() Dim WS1 As Worksheet Dim rng1 As Range Dim rng2 As Range Dim rng3 As Range Dim rng4 As Range Set WS1 = Sheets("Filter") Set rng1 = WS1.Range("F5:G20") Set rng2 = WS1.Range("L2") With Application ScreenUpdating = False End With With WS1 rng1.ClearContents If rng2.Value = "(all)" Then Set rng3 = WS1.Range("V:V") rng3.AdvancedFilter Action:=xlFilterCopy, _ CopyToRange:=.Range("G5"), _ Unique:=True Else Set rng4 = WS1.Range("U:V") rng4.AdvancedFilter Action:=xlFilterCopy, _ CriteriaRange:=.Range("F1:F2"), _ CopyToRange:=rng4, _ Unique:=True End If rng1.Sort Key1:=rng1, _ Order1:=xlAscending, _ Header:=xlGuess, _ OrderCustom:=1, _ MatchCase:=False, _ Orientation:=xlTopToBottom End With With Application .ScreenUpdating = True End With End Sub |
Thread Tools | Search this Thread |
Display Modes | |
|
|
![]() |
||||
Thread | Forum | |||
Advance Filter | Excel Discussion (Misc queries) | |||
Advance Filter | Excel Discussion (Misc queries) | |||
Code to Advance filter a list in a shared workbook | Excel Programming | |||
Advance filter search does not filter an exact match | Excel Programming | |||
about advance filter | New Users to Excel |