Using VBA to Get a Sorted List only in Memory

You may place the following code on Sheet1 Module so that once you select any cell in the range I2:I33, the code will update the dropdown list in this range with a sorted version of your named range UnsortedRng.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.CountLarge > 1 Then Exit Sub
Dim x   As Variant
Dim y() As Variant
Dim i   As Long

x = Range("UnsortedRng").Value
ReDim y(1 To UBound(x, 1))
If Not Intersect(Target, Range("I2:I33")) Is Nothing Then
    For i = 1 To UBound(x, 1)
        y(i) = x(i, 1)
    Next i
    y = SortedArray(y)
    With Range("I2:I33").Validation
        .Delete
        .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
                            xlBetween, Formula1:=Join(y, ",")
    End With
End If
End Sub

Function SortedArray(ByVal Arr As Variant)
Dim i   As Long
Dim j   As Long
Dim Tmp As Variant

For i = LBound(Arr, 1) To UBound(Arr, 1)
    For j = i To UBound(Arr, 1)
        If Arr(i) > Arr(j) Then
            Tmp = Arr(i)
            Arr(i) = Arr(j)
            Arr(j) = Tmp
        End If
    Next j
Next i

SortedArray = Arr
End Function

Select allOpen in new window

SortRange.xlsm

Subodh Ji,

The above Code is working GREAT.
One question.

What if the Validation List is to be used in some other sheet, other than Sheet1 ?
Where the above code needs to be placed then ?

I am to apply Validation in range "VehName" on Sheet2

Also in Sheet2, I have one more UnsortedRng2 which will be Sorted using the above Code.

Now is it possible to use the above code for two Unsorted ranges ?

Then place this code on that other sheet not on Sheet1.
I used selection change event code just to automate the process of updating the dropdown lists in a specific range once a cell in that range is selected.
Otherwise you may convert this code to a regular sub-routine and place it on a Standard Module and then you can run it whenever it is required.

To sum Up.

UnsortedRng1 is based on Sheet1
SortedRng1 is to be used in Validation Range on Sheet2

UnsortedRng2 is based on Sheet2
SortedRng2 is to be used in Validation Range on Sheet3

I apologize for the mess up. I could not perceive the above scenario in original question.

Then place this code on that other sheet not on Sheet1.

I tried that. But it is giving an error there.
"Method Range of Object Failed" and it takes me to

x = Range("UnosrtedRng").Value

Then place this code on Sheet2 Module and change the validation range and the named range in the code.

I have tried to do it. But something is amiss.
I have uploaded the File.
SortRange1.xlsm

Please test the attached.
SortRange2.xlsm

Try this.

x = Sheets("Sheet1").Range("UnosrtedRng").Value

Select allOpen in new window

@Neeraj Ji,
When I open the file SortRange2.xlsm, it gives an error message, "We found a problem with some content in 'SortRange2.xlsm'. Do you want us to recover as much as we can ? If you trust the source of this workbook, click Yes."

If I open it in OFFICE 365, on clicking Yes, the file is opened and all the TABLES deleted.
If I open it in Excel 2010, on clicking Yes, the file is opened, and all the validations are gone, though the TABLES are intact.

Kindly tell what should I use as Named Range as Source in List validation criteria.

Yes, that's strange. I never opened any file again which I uploaded here and they are all having the same issue.
I have changed the approach now and inserted a new sheet called "List" with two named ranges called "SortedRange1" and "SortedRange2" and these named ranges would be updated once you select the validation ranges with sorted list of your Unsorted Named Ranges and then update the validation list in the validation ranges.

For more details, please refer to the attached.
SortRange-v3.xlsm

Thanks Sir !
It is not giving any error. But I am not certain as to how it is working.
I added one name in Sheet2, but there was no change in SortedRange2
SortRange-v3.xlsm

No, that's not true. The name "Avninderpal Singh" you added in UnSortedRng2 on Sheet2 is reflected in the dropdown list in the range ValidRng2 on Sheet2 correctly if you select a cell in ValidRng2.

If you don't change the selection in the ValidRng2 after making the changes in the UnSortedRng2, the dropdowns in ValidRng2 won't be updated.

If you don't change the selection in ValidRng2 and expand the already existing dropdown in the already activated cell in the ValidRng2, the values in the dropdown will not get updated.

The code is for Selection Change Event, so it will only get triggered if you change the selection in the target range which is ValidRng2 in this case.

Dear Subodh Ji,

Something is definitely amiss. It is not happening.

What I understood till now-->
When I add any name in UnsortedRng1 or UnsortedRng2, and then AS SOON AS I select any Cell in ValidRng1 or ValidRng2, the code is supposed to update the SortedRng1 or SortedRng2, which is the source of the Validation List in ValidRng1 or ValidRng2

But it certainly is not happening.

I have attached the file.
Names were added, but those are not updated in the SortedRng1 or SortedRng2.


I have also tried a Power Query solution too, which is working. But I wish the VBA Solution TOO. It remains my first choice
Also how to refresh the Power Query automatically (via some setting in Power Query or via VBA) when the power query source data is Updated.
I always preferred the solution where the List is only in VBA memory and not in any additional list.
SortRange-v3.xlsm

Thanks Subodh Ji,

I am not sure what was missing in the earlier file but this one is working.

1. Also I am quite intrigued about that error. When I Closed that file before uploading, there was no error, but today when I am opening the same file, it is giving an error.
Any guess what is happening ? I am scared ??

2. Where the Change Event Code is to be placed ?

I placed the following code in SortedList Sheet, but it does not refresh when the table is updated (SortedList is the name of the sheet on which the Data query is saved)

Private Sub Worksheet_Change(ByVal Target As Range)
ThisWorkbook.RefreshAll
End Sub
SortRange-v4.xlsm

First of all, that's not how you should proceed in your question and you should stick to the original question you asked.
And if you have some other questions, you can always open a new question at any time.

Now to the question you asked about Power Query.
If you place the Change Event code on the Sheet Module with the Tables returned by the Power Query, it will be an infinite loop and will never end as once the power query returns the data on the sheet, the change event on the same sheet module will be triggered again and start refreshing the query again and this process will never end.

I asked you to place the Change Event code in the sheet module with the source data for the power query.
So you are supposed to place that change event code on the Sheet1 and Sheet2 Modules only so that when you change the data in the source tables, that will update the Power Query Tables on SortedList Tab.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim tbl As ListObject
Set tbl = ActiveSheet.ListObjects(1)
On Error GoTo Skip
If Not Intersect(Target, tbl.DataBodyRange.Columns(3)) Is Nothing Then
    Application.EnableEvents = False
    ThisWorkbook.RefreshAll
End If
Skip:
Application.EnableEvents = True
End Sub

Select allOpen in new window

SortRange-v5.xlsm

First of all, that's not how you should proceed in your question and you should stick to the original question you asked.
And if you have some other questions, you can always open a new question at any time.

In the enthusiasm, I missed the protocol. Apologies for that !!
I am quite naive in VBA, so could not understand where to place the code.

Thanks a Lot Sir !!!

You're welcome Kanwal! Glad I could help.