excel vba多列查重并计算重复行 指定列的和

Sub main()

'需要组成关键字的列
Dim key() As Variant
key = Array("A3:A929", "B3:B929", "C3:C929", "F3:F929")

'需要累计的列
Dim total() As Variant
total = Array("X3:X929", "AB3:AB929", "AD3:AD929")

Dim sheetname$, writesheet$
sheetname = "Sheet1" '目标账薄名字
writesheet = "Sheet3" '结果写入账薄名字


Dim m&, n&
m = UBound(key)
n = UBound(total)
Dim temparr As Range, totalnum&
Set temparr = Worksheets(sheetname).Range(key(0))
totalnum = temparr.Rows.Count
Set temparr = Nothing

Set dic = CreateObject("Scripting.Dictionary")
Dim j%, tempstr$, temp As Range
Dim keyStr() As Variant
ReDim keyStr(totalnum)
For i = 1 To totalnum
tempstr = ""
For j = 0 To m
Set temp = Worksheets(sheetname).Range(key(j))
If temp(i) = "" Then
tempstr = tempstr & "#" & "?"
Else
tempstr = tempstr & "#" & CStr(temp(i))
End If

Next
dic(tempstr) = ""
keyStr(i - 1) = tempstr
Next




Dim num&, k&, sum#, temp1 As Range

num = dic.Count

For i = 1 To num
Dim arrtemp1 As Variant
Dim arrtemp2 As Variant
arrtemp2 = dic.keys
arrtemp1 = Split(CStr(arrtemp2(i - 1)), "#")
For j = 1 To UBound(arrtemp1)
Worksheets(writesheet).Cells(i, j).Value = arrtemp1(j)
Next

For j = 0 To n
Set temp1 = Worksheets(sheetname).Range(total(j))
sum = 0#
For k = 0 To totalnum - 1
If (arrtemp2(i - 1) = keyStr(k)) Then
sum = sum + CDbl(temp1(k + 1, 1))
End If
Next
Worksheets(writesheet).Cells(i, UBound(arrtemp1) + 1 + j).Value = sum
Next


Next
Set dic = Nothing



End Sub