excel運(yùn)用VBA生成無(wú)重復(fù)無(wú)空值的數(shù)據(jù)有效果性下拉下文

在Excel工作表的某個(gè)單元格中應(yīng)用數(shù)據(jù)有效性設(shè)置來(lái)制作下拉列表時(shí)，如果引用的行或列區(qū)域中包含空單元格或重復(fù)項(xiàng)，那么在有效性下拉列表中會(huì)與原區(qū)域中的內(nèi)容完全相同，也會(huì)包含空值或重復(fù)項(xiàng)，顯得有些不夠美觀。例如下圖是A1單元格的一個(gè)下拉列表。

???

??? 通常可以去掉重復(fù)項(xiàng)和空單元格后再設(shè)置數(shù)據(jù)有效性，但如果不想改變單元格的結(jié)構(gòu)，可以使用下面的VBA代碼來(lái)解決這個(gè)問(wèn)題，假如要設(shè)置下拉列表的單元格為D5，數(shù)據(jù)區(qū)域?yàn)镵8:K38，步驟如下：

??? 1.按Alt+F11，打開(kāi)VBA編輯器。

??? 2.在“工程”窗口中雙擊要包含數(shù)據(jù)有效性設(shè)置的工作表，在右側(cè)代碼窗口中輸入下列代碼：

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
?? Dim RowNum, ListRows, ListStartRow, ListColumn As Integer
?? Dim TheList As String
?? Dim Repeated As Boolean
?? If Target.Address <> "$D$5" Then Exit Sub
?? With Range("k8:K38")
????? ListRows = .Rows.Count
????? ListStartRow = .Row
????? ListColumn = .Column
?? End With
?? For RowNum = 0 To ListRows - 1
????? Repeated = False
????? If Not IsEmpty(Cells(ListStartRow + RowNum, ListColumn)) Then
??????? For i = 0 To RowNum - 1
????????? If Cells(ListStartRow + RowNum, ListColumn) = Cells(ListStartRow + i, ListColumn) Then
??????????? Repeated = True
??????????? Exit For
????????? End If
??????? Next i
??????? If Not Repeated Then TheList = TheList & Cells(ListStartRow + RowNum, ListColumn) & ","
????? End If
?? Next RowNum
?? TheList = Left(TheList, Len(TheList) - 1)
?? With Range("D5").Validation
????? .Delete
????? .Add _
????? Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:=xlBetween, Formula1:=TheList
?? End With
End Sub

??? 3.關(guān)閉VBA編輯器返回Excel界面，選擇D5單元格，單擊下拉箭頭即可看到不包含空值和無(wú)重復(fù)的下拉列表。

??? 說(shuō)明：上述代碼使用了工作表的SelectionChange事件，當(dāng)在工作表中重新選擇單元格后會(huì)執(zhí)行上述代碼。需根據(jù)實(shí)際將代碼中的單元格“D5”和區(qū)域“k8:K38”進(jìn)行更改。