Hello Excel Experts,
First of all, i thank each member for their suggestion to figure out issue in below post. Currently i am too struggling with same issue and after going through below post i found the issue was with drop downs..!!
https://www.excelforum.com/excel-pro...ver-as-mu.html
Below is the code which i currently use to get sorted unique values and its definitely causing the error. How can i create drop downs (Unique and Sorted) using Array formulas and in-turn add into VBA..? Sorry i am not that used to Array formulas.. Please help.. By the way i have 5000 rows (dynamic) of data and unique values among them may be 50 or so..
Function Sorted(Rng As Range, Optional Delim As String = ",") As String
Dim X As Long, Arr As Variant, Uniques As Variant
Arr = Rng.Value
With CreateObject("Scripting.Dictionary")
For X = 1 To UBound(Arr)
.Item(Arr(X, 1)) = 1
Next
Uniques = .Keys
End With
With CreateObject("System.Collections.ArrayList")
For X = LBound(Uniques) To UBound(Uniques)
.Add Uniques(X)
Next
.Sort
Sorted = Join(.ToArray, Delim)
If left(Sorted, 1) = Delim Then Sorted = Mid(Sorted, 2)
End With
End Function
Sub allDropDowns()
Dim unique_string1 As String
Worksheets("SOMENAME").Select
On Error Resume Next
Worksheets("SOMENAME").ShowAllData
On Error GoTo 0
unique_string1 = Sorted(Worksheets("SOMENAME").Range("A4", Worksheets("SOMENAME").Cells(Rows.Count, "A").End(xlUp)))
With Worksheets("Macro").Range("C9").Validation
.Delete
.Add xlValidateList, Formula1:="All," & unique_string1
End With
Bookmarks