|
发表于 2014-3-11 16:20
|
显示全部楼层
本楼为最佳答案
KDZ 发表于 2014-3-11 16:03
你好,谢谢你!不过运行后又出现“溢出的问题”,请您再看一看,谢谢! - Sub 删除重复行()
- Dim d, rng As Range, i&, p$, j%
- Set d = CreateObject("scripting.dictionary")
- With Sheets("sheet2")
- row1 = .Range("E" & .Rows.Count).End(xlUp).Row
- arr = .Range("Q4:AE" & row1)
- arr1 = .Range("A4:H" & row1)
- End With
- For i = 1 To UBound(arr)
- p = ""
- For j = 1 To UBound(arr, 2)
- p = p & "," & IIf(IsError(arr(i, j)), 0, arr(i, j))
- Next
- If Not d.exists(p) Then
- d(p) = ""
- Else
- If rng Is Nothing Then Set rng = Cells(i, 1) Else Set rng = Union(rng, Cells(i, 1))
- End If
- Next
- '前几列名称重复且22、29列为空,挑选17-19三列含1靠前的行
- For j = 3 To 1 Step -1
- For i = 1 To UBound(arr)
- If arr(i, 6) = "" And arr(i, 13) = "" And IIf(IsError(arr(i, j)), 0, arr(i, j)) = 1 Then
- p = arr1(i, 1) & "," & arr1(i, 2) & "," & arr1(i, 5) & "," & arr1(i, 8)
- If Not d.exists(p) Then
- d(p) = i
- Else
- If rng Is Nothing Then Set rng = Cells(i, 1) Else Set rng = Union(rng, Cells(i, 1))
- d(p) = i
- End If
- End If
- Next
- Next
- If Not rng Is Nothing Then rng.EntireRow.Delete
- End Sub
复制代码 |
|