|
发表于 2014-3-14 09:57
|
显示全部楼层
本楼为最佳答案
我也搞不懂为什么,只能把一个数组拆分为3个小数组,不然就溢出。另外注意到你QRS列里面有非数字类型,代码中也一并考虑了。- Sub 删除重复行()
- Dim d, DelRng As Range, i&, p$, j%
- Dim arr
- Set d = CreateObject("scripting.dictionary")
- With ActiveSheet
- Set DelRng = .Rows(65536)
- row1 = .Range("E" & .Rows.Count).End(xlUp).Row
- arr = .Range("A5:I" & row1) '前几列
- brr = .Range("q5:s" & row1) '17 18 19列
- crr = .Range("v5:ac" & row1) '22--29列
- For i = 1 To UBound(arr)
- If Len(crr(i, 1)) = 0 And Len(crr(i, 8)) = 0 Then '在22列、29列都为空的行中筛选
- p = arr(i, 1) & "," & arr(i, 2) & "," & arr(i, 5) & "," & arr(i, 8)
- d(p) = d(p) & "," & i '把1、2、5、9四列相同的行放入字典
- End If
- Next
- dk = d.items
- For k = 0 To UBound(dk)
- xrr = Split(dk(k), ",")
- s1 = 0: s2 = 0 's1判断19列是否有数,s2判断18列是否有数
- k1 = 0: k2 = 0: k3 = 0
- For m = 1 To UBound(xrr)
- i = Val(xrr(m))
- s1 = s1 + IIf(IsNumeric(brr(i, 3)), brr(i, 3), 1) '考虑到QRS列有非数字情形
- s2 = s2 + IIf(IsNumeric(brr(i, 2)), brr(i, 2), 1)
- Next
- For m = 1 To UBound(xrr)
- i = Val(xrr(m))
- If s1 > 0 Then
- If brr(i, 3) = 0 Then
- Set DelRng = Union(DelRng, .Rows(i + 4))
- Else
- k1 = k1 + 1
- If k1 > 1 Then Set DelRng = Union(DelRng, .Rows(i + 4))
- End If
- ElseIf s2 > 0 Then
- If brr(i, 2) = 0 Then
- Set DelRng = Union(DelRng, .Rows(i + 4))
- Else
- k2 = k2 + 1
- If k2 > 1 Then Set DelRng = Union(DelRng, .Rows(i + 4))
- End If
- Else
- If brr(i, 1) = 0 Then
- Set DelRng = Union(DelRng, .Rows(i + 4))
- Else
- k3 = k3 + 1
- If k3 > 1 Then Set DelRng = Union(DelRng, .Rows(i + 4))
- End If
- End If
- Next
- Next
- DelRng.Delete
- End With
- End Sub
复制代码 |
|