作者:乔山办公网日期:
返回目录:excel表格制作
'利用字典去重,下面是示例代码,去除第一列的重复项Sub Test() '利用字典去重,字典的特性是key值不能重复
Dim Dic, Arr
Dim i As Integer, r As Integer
Dim Str As String
r = Sheet1.Range("A65536").End(xlUp).Row
If r = 1 Then Exit Sub '如果第一列没有数据那么e68a84e8a2ad7a64363退出程序
Set Dic = CreateObject("scripting.dictionary") '创建字典对象
For i = 1 To r '将第一列数据添加到字典的key值中
Dic(CStr(Cells(i, 1))) = ""
Next
Arr = Dic.keys '返回字典key的数组
Set Dic = Nothing '销毁对象
Str = Join(Arr, ",") '将数组中的内容显示为一字符串
MsgBox Str
End Sub
A列最后一行
LastRow = Cells(Rows.Count, 1).End(xlUp).Row
B列最后一行
LastRow = Cells(Rows.Count, 2).End(xlUp).Row
Dim C_Address, D_Address
If [e15].End(xlUp).Row > 1 Then
For i = 2 To [e15].End(xlUp).Row
Set c = [G1:U1].Find(Range("E" & i), , , xlWhole)
If Not c Is Nothing Then
C_Address = c.Address
temp = ""
Do
temp = temp & Split(c.Address, "$")(1) & ","
Set c = [G1:U1].FindNext(c)
Loop While Not c Is Nothing And c.Address <> C_Address
End If
If temp <> "" Then
arr = Split(Left(temp, Len(temp) - 1), ",")
For j = 0 To UBound(arr)
Set D = Range(arr(j) & "2:" & arr(j) & "15").Find(Range("d" & i), , , xlWhole)
If Not D Is Nothing Then
D_Address = D.Address
Do
Range("B" & D.Row) = Range("B" & D.Row) + 1
Set D = Range(arr(j) & "2:" & arr(j) & "15").FindNext(D)
Loop While Not D Is Nothing And D.Address <> D_Address
End If
Next
End If
Next
End If