The following code clears 11 columns that contain lists that are used for data validation on another worksheet. This list is constantly changing due to records being added/removed from the source data. Basically I'm checking every value in a column, and if it meets all of my criteria I'm adding it to my data validation column (after making sure it doesn't already exist there).
Once I've populated my 11 columns, I sort each one alphabetically. This is the portion of my code that I don't like. While it doesn't explicitly select
anything, I can see on my worksheet the last column that was sorted because it is selected.
What I'm wondering is if there's a better way to dynamically generate this list alphabetically without having to use .Sort
- possibly populating an array and then sorting it alphabetically? I've never sorted an array in VBA, so I'm not sure 1. If it's even more efficient and 2. What is the best method to sort an array, if that's the approach I should take here?
Code:
Option Explicit
Sub PopulateAllDataValidationLists()
Dim sht As Worksheet, sht2 As Worksheet
Dim i As Long, listcol As Long
Set sht = Worksheets("Sheet 1")
Set sht2 = Worksheets("Data Validation")
Application.ScreenUpdating = False
sht2.Range("A2:K500").ClearContents
For i = 1 To 11
listcol = sht.Rows("1:1").Find(What:=sht2.Cells(1, i).Value, LookAt:=xlWhole).Column
For j = 2 To sht.Cells(sht.Rows.Count, listcol).End(xlUp).Row
If IsError(Application.Match(sht.Cells(j, listcol).Value, sht2.Range(ColumnLetter(i) & ":" & ColumnLetter(i)), 0)) And _
sht.Cells(j, listcol).Value <> "" And _
sht.Cells(j, listcol).Value <> "UNK" And _
sht.Cells(j, listcol).Value <> "xxx" And _
sht.Cells(j, listcol).Value <> "yyy" And _
sht.Cells(j, listcol).Value <> "zzz" And _
sht.Cells(j, listcol).Value <> "yxz" And _
sht.Cells(j, listcol).Value <> "zyx" And _
Len(sht.Cells(j, listcol).Value) <= 3 Then
sht2.Cells(sht2.Cells(sht2.Rows.Count, i).End(xlUp).Row + 1, i).Value = sht.Cells(j, listcol).Value
End If
Next j
Next i
'Sort all alphabetically
For i = 1 To 11
Worksheets("Data Validation").Sort.SortFields.Clear
Worksheets("Data Validation").Sort.SortFields.Add Key:=Range(ColumnLetter(i) & "2"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
With Worksheets("Data Validation").Sort
.SetRange Range(ColumnLetter(i) & "2:" & ColumnLetter(i) & sht2.Cells(sht2.Rows.Count, i).End(xlUp).Row)
.Header = xlNo
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
Next i
Application.ScreenUpdating = True
End Sub
Function ColumnLetter(colnum As Long) As String
ColumnLetter = Split(Cells(1, colnum).Address, "$")(1)
End Function
An example of data in a column:
ArrayList
and raise events whenever the order or content change. You might be able to make use of the working branch though. Message me on chat if you'd like some more details, the whole thing will hopefully go up on CR fairly soon \$\endgroup\$