Sort virtual range

[Vincent88]

Hi Guy,
I want to sort a virtual list. Can someone help me out.
The virtual object list refers to dynamic range in Column L. I want to sort the object list only (not actual list in column L) from A to Z and assign a name to it for dropdown list purpose. What should be corrected ?

Sub sortedVirtualListAgent1()

Dim rngSort As Range
Dim lr As Long
Dim Agtarray As Object
Dim rngL As Range

'Creating a array list
Set Agtarray = CreateObject("System.Collections.ArrayList")

'Physical Source in Column L
lr = Sheets("Data").Cells(Rows.Count, "L").End(xlUp).Row
Set rngL = Sheets("Data").Range("L2:L" & lr)

  'Set reference to the Table and column to be sorted
      Set Agtarray = rngL
    

  'Use Range.Sort method to apply sort
    
    Agtarray.Sort _
    Key1:=rngSort, _
    Order1:=xlAscending, _
    Header:=xlYes
        
    sorted_array = Agtarray.toarray
    
    End Sub

 

[Fluff]

How about

Sub sortedVirtualListAgent1()

Dim rngSort As Range
Dim lr As Long
Dim Agtarray As Object
Dim Cl As Range
Dim Sorted_array As Variant

'Creating a array list
Set Agtarray = CreateObject("System.Collections.ArrayList")

'Physical Source in Column L
lr = Sheets("Data").Cells(Rows.Count, "L").End(xlUp).Row
   For Each Cl In Sheets("Data").Range("L2:L" & lr)
      If Not Agtarray.contains(Cl.Value) Then Agtarray.Add Cl.Value
   Next Cl
   Agtarray.Sort
   Sorted_array = Agtarray.toarray
    
End Sub

 

[Vincent88]

Hi Fluff,
It seems the code does not extract the value to the object !

 

[Fluff]

What do you mean? That image clearly shows that the Sorted_array contains 13 values.

 

[Vincent88]

Hi Fluff, How to apply it to the dropdown list ?

Sub drop_down()

Dim ws As Worksheet
Set ws = ActiveSheet
Dim lrow As Long
lrow = ws.Cells(Rows.Count, "A").End(xlUp).Row



Debug.Print lrow

With ws.Range("A3:A" & lrow).Validation
 .Delete
 .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, _
 Operator:=xlBetween, Formula1:="=Sorted_array"   
 .IgnoreBlank = True
 .InCellDropdown = True
 .InputTitle = ""
 .ErrorTitle = ""
 .InputMessage = ""
 .ErrorMessage = ""
 .ShowInput = True
 .ShowError = True
End With


End Sub

 

[Akuini]

I want to sort the object list only (not actual list in column L) from A to Z and assign a name to it for dropdown list purpose.

Do you mean you want an array (not a range) as the source of data validation?
AFAIK, you can't, unless you convert the array to a string. But using string as the source of data validation is limited to 256 (or 255?) characters.
The work-around would be using a helper column where you sort the list and then use the helper column as the source of data validation .

 

[Fluff]

As Akuini has said, you can't do that. You would need to output the array to a sheet & then use that in the data validation.

 

[Vincent88]

Hi Fluff,
I use this code to paste but it doesn't work
Sheets("Data").Range("N2").Resize(UBound(Sorted_array), 1).Value = Sorted_array

 

[Fluff]

You need to transpose the array like

   Sheets("Data").Range("N2").Resize(UBound(Sorted_array), 1).Value = Application.Transpose(Sorted_array)

 

[Vincent88]

Hi Fluff,
The code did not copy all cells to array- missing one row. Please help.

Sub sortedVirtualListAgent1()

Dim lr As Long
Dim Agtarray As Object
Dim Cl As Range
Dim Sorted_array As Variant


'Creating a array list
Set Agtarray = CreateObject("System.Collections.ArrayList")

'Physical Source in Column L
lr = Sheets("Data").Range("L1").End(xlDown).Row
Debug.Print lr
   For Each Cl In Sheets("Data").Range("L1:L" & lr)
      If Not Agtarray.contains(Cl.Value) Then Agtarray.Add Cl.Value
  Next Cl
  
Agtarray.Sort
Sorted_array = Agtarray.toarray

'Output Sorted_array to Cells
Sheets("Data").Range("N1").Resize(UBound(Sorted_array), 1).Value = Application.Transpose(Sorted_array)

End Sub