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
Paul
Sin wrote:

It's definitely "(All)" - in brackets, would this cause troble? I just need
to distinguish the "all" from other strings in the list so that it's always
at the top of the list.

From my test, it seems the "IF" code is not working in that it will run with
the criteria even if the selected value is "(all)", as such, resulted in 0
record.

Any suggestions???

Thanks in advance.
Sin


" wrote:

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

Sin wrote:

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

" wrote:

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

Sin wrote:

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