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

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

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?

"(E-Mail Removed)" 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
>
>

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

"(E-Mail Removed)" 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
>
>

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
>
> "(E-Mail Removed)" 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
> >
> >

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
>
>
> "(E-Mail Removed)" 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
> > >
> > > "(E-Mail Removed)" 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
> > > >
> > > >
> >
> >