|
楼主 |
发表于 2020-3-12 11:08
|
显示全部楼层
[广告] VBA代码宝 - VBA编程加强工具 · VBA代码随查随用 · 内置多项VBA编程加强工具 ★ 免费下载 ★ ★ 使用手册★
请参考
Option Explicit '所有的变量使用前均要先定义
Function GetUniqueList(mode As Integer, x As Integer, ParamArray Rngs() As Variant) '返回唯一值列表的函数
Dim UniqueListCount As Long, i As Long, j As Long, k As Long, Cnt1 As Long, Cnt2 As Long, m As Integer
Dim Wksht As Worksheet, singleArea As Range, cell As Range, arr
Dim ListArray() As Variant, Wkshtfun As WorksheetFunction
Application.Volatile True '定义为易失函数
Set Wkshtfun = Application.WorksheetFunction
i = 0
'求出各个区域中非空白单元格总数
For j = 0 To UBound(Rngs)
i = i + Wkshtfun.CountA(Rngs(j))
Next j
'如果没有非空白单元格运行下段代码后结束
If i = 0 Then
GetUniqueList = ""
Exit Function
End If
'如果有非空白单元格运行下面代码
ReDim ListArray(1 To i)
UniqueListCount = 0
For m = 0 To UBound(Rngs) '循环每个区域
Set singleArea = Rngs(m)
Set Wksht = Rngs(m).Parent '定义引用区域的工作表
If Wkshtfun.CountA(singleArea) <> 0 Then
Set singleArea = Intersect(Wksht.UsedRange, singleArea) '定义引用范围中的已用区域
With singleArea
If mode = 0 Then
Cnt1 = .Rows.Count: Cnt2 = .Columns.Count
Else
Cnt1 = .Columns.Count: Cnt2 = .Rows.Count
End If
For i = 1 To Cnt1
For j = 1 To Cnt2
If mode = 0 Then
Set cell = .Cells(i, j)
Else
Set cell = .Cells(j, i)
End If
If cell <> "" Then '非空白单元格才运行
If UniqueListCount = 0 Then '列表的首个值才运行
ListArray(1) = cell
UniqueListCount = 1
GoTo ExitLoop
End If
For k = 1 To UniqueListCount
If ListArray(k) = cell Then GoTo ExitLoop '判别是否为重复值
Next k
UniqueListCount = UniqueListCount + 1
ListArray(UniqueListCount) = cell
End If
ExitLoop:
Next j
Next i
End With
End If
Next m
On Error Resume Next
'求出多单元格数组公式输入区域的最大行或列数
i = Wkshtfun.Max(Application.Caller.Rows.Count, Application.Caller.Columns.Count)
'如果是输入在一个单元格中就返回完整的唯一值列表数组
If i = 1 Then
ReDim Preserve ListArray(1 To UniqueListCount)
arr = ListArray
GetUniqueList = arr(x)
Exit Function
End If
'如果是输入在一个多单元格区域中就返回一个与输入区域相适应的数组
ReDim Preserve ListArray(1 To i) '重定义数组尺寸大小并保留已有的值
If i > UniqueListCount Then '对超出唯一值列表数的部分赋空值
For j = UniqueListCount + 1 To i
ListArray(j) = ""
Next j
End If
arr = ListArray
GetUniqueList = arr(x) '将数组作为函数的返回值
End Function
A14=getuniquelist(1,ROW(A1),K:L,$N$8:$O$11)
F14=getuniquelist(0,ROW(A1),K:L,$N$8:$O$11) |
评分
-
1
查看全部评分
-
|