View Single Post
  #1   Report Post  
Posted to microsoft.public.excel.programming
Sin Sin is offline
external usenet poster
 
Posts: 6
Default Advance Filter & If Code

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