Advance Filter & If Code

[Guest]

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

 

[paul.robinson]

Hi
Tidied up your code a bit:

Private Sub DropDown83_Change()
Dim WS1 As Worksheet
Dim rng1 As Range
Dim rng2 As Range
Dim rng3 As Range
Dim rng4 As Range

Application.ScreenUpdating = False
Set WS1 = Sheets("Filter")
With WS1
Set rng1 = .Range("F5:G20")
Set rng2 = .Range("L2")
On Error Resume Next 'required if Advanced filter used
.ShowAllData
On Error GoTo 0
rng1.ClearContents
If rng2.Value = "(all)" Then
Set rng3 = .Range("V:V")
rng3.AdvancedFilter Action:=xlFilterCopy, _
CopyToRange:=.Range("G5"), _
Unique:=True
Else
Set rng4 = .Range("U:V")
rng4.AdvancedFilter Action:=xlFilterCopy, _
CriteriaRange:=.Range("F1:F2"), _
CopyToRange:=rng4, _
Unique:=True
End If
End With
rng1.Sort Key1:=rng1, _
Order1:=xlAscending, _
Header:=xlGuess, _
OrderCustom:=1, _
MatchCase:=False, _
Orientation:=xlTopToBottom
End Sub

The .ShowAllData is there to remove filtering before you run the sub.
The first part of the If seems to work OK. Your second part is odd
because you are copying your filtered data back onto itself? I don't
know what you are trying to acheive with this, but things seem to work
if you copy to some other location than rng4.
regards
Paul

 

[Guest]

My data set is located in column U:V only, e.g

Colum U Col V
1 A
2 B
3 C
1 B
2 B
1 B
1 C
3 A

The drop down control was a of unique value from col U, what I want is that
when user chose a value in the drop down control, the advance filter will
produce the unique values of column U with the chosen value as criteria, if
user chose "All" in the drop down, the advance filter will produce unique
value with no criteria.

I've tried your code, but it also deletes my data in col U and V after it
run and also it does not produce any unique value as well. How could I
adjust the code so that it will do delete my data set? And is this problem
the cause of zero unique value results?

 

[Guest]

Sorry Paul, please ignore my last message, you're right, I've got the range
incorrect, the filtered data in facts should be pasted to "rng1" not "rng4".

However, there is still a problem with the code in that the advance filter
does not work in the 1st part (where no criteria was set), i.e. when I chose
"all", the advance filter returned nothing rather than all the unique value.
Could you help?

thanks
Sin

 

[paul.robinson]

Hi
The first part worked for me. In your first message you said you were
looking for "(all)", not "all".
That is two different strings? - it's a long shot!
regards
Paul

 

[paul.robinson]

Hi
As I said, your code worked fine for me with (all) in cell G2. If that
doesn't work for you then there is some problem with your data perhaps?
regards