validation list to sort rows

[buckwheat4948]

I use the following code, supplied by Alphafrog to create a validation list.
Is it possible to use it to also sort the same rows the validation list is building from when the user clicks an item in the validation list? maybe using the formula2?

Dim strList As String, vList As Variant
Dim i As Long, j As Long

'Uniques
For Each cell In Range("A11:A30")
If cell.Value <> "" Then
If InStr(1, strList, cell.Value & ",", 1) = 0 Then strList = strList & cell.Value & ","
End If
Next cell
strList = Left(strList, Len(strList) - 1)

'Sort
vList = Split(strList, ",")
For i = 0 To UBound(vList)
For j = i To UBound(vList)
If LCase(vList(j)) < LCase(vList(i)) Then
strList = vList(i)
vList(i) = vList(j)
vList(j) = strList
End If
Next j
Next i
strList = vList(0)
For i = 1 To UBound(vList)
strList = strList & "," & vList(i)
Next i

Application.ScreenUpdating = False

With Range("A5").Validation
.Delete
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, _
Operator:=xlBetween, Formula1:=strList
.IgnoreBlank = True
.InCellDropdown = True
End With

Range("A6").Formula = "=IF(A5="""","""",COUNTIF(A11:A30,A5))"

Application.ScreenUpdating = True


Thanks!

 

[Ruddles]

Can you clarify: the validation list is built from Range("A11:A30") and the validation list is sorted, but you'd like Range("A11:A30") to be sorted as well / at the same time? So if someone edits the list on the worksheet, next time it's used it gets sorted back into sequence?

If so, I think you'd just need to make this change:-

strList = vList(0)
[COLOR=red][B]Cells(11,1)=vList(0)
[/B][/COLOR]For i = 1 To UBound(vList)
  strList = strList & "," & vList(i)
[COLOR=red][B]  Cells(i+11,1)=vList(i)[/B][/COLOR]
Next i

As always, test on a copy of your workbook.

 

[buckwheat4948]

Sorry if i didn't explain it well, its not always easy to say what you want.

The validation list is sorted alphabetically, but id now like to add sorting the rows as well. So the validation list is built, sorted alphabetically and the user clicks an item in the list and now the same rows that the validation list was built from will sort with the item clicked in the validation list on top.

So A10:30 contains:
c
a
b
b
c
b
.
.
.

validation list now contains
a
b
c

user clicks b and the rows are sorted so all the b's are on top
b
b
b
c
a
c
.
.
.

clear as mud? lol...thanks for the reply btw