誰能給我VBA代碼，該代碼將從Excel工作表獲取一個范圍（行或列），并用唯一值填充列表/數(shù)組，即：tabletablechairtablestoolstoolstoolchair當宏運行時會創(chuàng)建一個數(shù)組，例如：fur[0]=tablefur[1]=chairfur[2]=stool
3 回答
呼啦一陣風
TA貢獻1802條經(jīng)驗 獲得超6個贊
在這種情況下，我總是使用這樣的代碼（只要確保您選擇的分度數(shù)不在搜索范圍內(nèi)即可）
Dim tmp As String
Dim arr() As String
If Not Selection Is Nothing Then
For Each cell In Selection
If (cell <> "") And (InStr(tmp, cell) = 0) Then
tmp = tmp & cell & "|"
End If
Next cell
End If
If Len(tmp) > 0 Then tmp = Left(tmp, Len(tmp) - 1)
arr = Split(tmp, "|")
阿晨1998
TA貢獻2037條經(jīng)驗 獲得超6個贊
Sub GetUniqueAndCount()
Dim d As Object, c As Range, k, tmp As String
Set d = CreateObject("scripting.dictionary")
For Each c In Selection
tmp = Trim(c.Value)
If Len(tmp) > 0 Then d(tmp) = d(tmp) + 1
Next c
For Each k In d.keys
Debug.Print k, d(k)
Next k
End Sub
拉風的咖菲貓
TA貢獻1995條經(jīng)驗 獲得超2個贊
將Tim的Dictionary方法與下面Jean-Francois的變量數(shù)組結(jié)合在一起。
您想要的陣列位于 objDict.keys
Sub A_Unique_B()
Dim X
Dim objDict As Object
Dim lngRow As Long
Set objDict = CreateObject("Scripting.Dictionary")
X = Application.Transpose(Range([a1], Cells(Rows.Count, "A").End(xlUp)))
For lngRow = 1 To UBound(X, 1)
objDict(X(lngRow)) = 1
Next
Range("B1:B" & objDict.Count) = Application.Transpose(objDict.keys)
End Sub
添加回答
舉報
0/150
提交
取消