excel - 从多个工作表的同一列创建唯一的单词列表，并将其按字母顺序排序到动态命名范围中

Question

我有一个用户表单，其中有一个组合框，其中包含名称列表供用户选择。该列表需要是动态的，因为我希望用户能够在组合框中键入一个名称（如果它不存在），但也需要一个已经存在的名称列表供他们选择。几年前我在这里发现了一个类似的问题：Excel - All unique words in a range

这里的问题是它只适用于一张纸，我看不到如何使用它从多张纸中提取，或者如何按字母顺序自动对名称进行排序。

这是我现在用来创建动态范围的子程序，原始创建者的链接被保留在应得的地方给予信任。

Sub SupplierListDynamic()
'Source: https://powerspreadsheets.com/
'For further information: https://powerspreadsheets.com/vba-create-named-range/
'declare object variable to hold reference to worksheet containing cell range
Dim myWorksheet As Worksheet

'declare variables to hold row and column numbers that define named cell range (dynamic)
Dim myFirstRow As Long
Dim myLastRow As Long
Dim myFirstColumn As Long
Dim myNamedRangeDynamic As Range    'declare object variable to hold reference to cell range
Dim myRangeName As String   'declare variable to hold defined name


Set myWorksheet = ThisWorkbook.Worksheets("List_Data")  'identify worksheet containing cell range
myFirstRow = 2  'identify first row and first column of cell range
myFirstColumn = 9
myRangeName = "SupplierList"    'specify defined name

With myWorksheet.Cells
    'find last row and last column of source data cell range
    myLastRow = .Find(What:="*", LookIn:=xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    'myLastColumn = .Find(What:="*", LookIn:=xlFormulas, LookAt:=xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlPrevious).Column

    'specify cell range
    Set myNamedRangeDynamic = .Range(.Cells(myFirstRow, myFirstColumn), .Cells(myLastRow, myFirstColumn))
End With

'create named range with workbook scope. Defined name is as specified.
'Cell range is as identified, with the last row and column being dynamically determined
ThisWorkbook.Names.Add Name:=myRangeName, RefersTo:=myNamedRangeDynamic
End Sub

但它没有排序，因为我{=INDEX(Sep[SUPPLIER],MATCH(0,COUNTIF($I$1:I1,Sep[SUPPLIER]),0))}用来从另一张表中提取唯一值，当我对它们进行排序时，它们只是回到原来的顺序，因为公式是如何工作的。我宁愿这一切都用 VBA 完成，以将其保留在后台，但我需要一些帮助将所有内容联系在一起。

标签： excelvbaexcel-formula

Answer 1

所以我最终弄清楚了这一点。我最终做的只是将供应商名称的字符串值复制到下一列，然后清除原始列并将字符串值粘贴回其中。然后我使用上面的过程动态创建命名范围，并使用下面的过程对范围进行排序。

这让我可以将新供应商添加到列表中并相应地更新列表。我使用的两个支持程序如下。希望这将有助于将来的人。

Public Sub AddnewSupplier(supplier As String)
    Dim newRng As Range, supplierListColumn As Integer

    supplierListColumn = 9

    Set newRng = SelectFirstBlankCell(supplierListColumn, "List_Data")

    newRng.Value = supplier

    Call SupplierListDynamic
    Call sortSupplierList

End Sub

Public Sub sortSupplierList()
    Range("SupplierList").Sort Key1:=Worksheets("List_Data").Range("I1"), Order1:=xlAscending, Header:=xlYes
End Sub