excel vba快速删除重复项

发布网友发布时间：2022-04-29 20:28

共1个回答

热心网友时间：2022-06-22 10:00

Sub xx()
  Set d1 = CreateObject("scripting.dictionary")
  Set d2 = CreateObject("scripting.dictionary")
  With [AF666:AO866]
    .Interior.ColorIndex = xlNone
    For i = 1 To .Count
      d1(.Item(i).Text) = ""
    Next
  End With
  With [A39:J1038]
    For i = 1 To .Count
      If d1.exists(.Item(i).Text) Then
        d2(.Item(i).Text) = ""
        .Item(i).ClearContents
      End If
    Next
  End With
  With [AF666:AO866]
    For i = 1 To .Count
      If d2.exists(.Item(i).Text) Then
        .Item(i).Interior.ColorIndex = 7
      End If
    Next
  End With
End Sub

追问

现在区域1的空单元变为紫色了

先将区域1还原单元为无色，（区域1有部分单元或空单元有颜色，所以先还原为无色）

2。删除区域2相同的（见红色示）

3。区域1相同的填充紫色（见紫色示）

追答Sub xx()
  Set d1 = CreateObject("scripting.dictionary")
  Set d2 = CreateObject("scripting.dictionary")
  With [AF666:AO866]
    .Interior.ColorIndex = xlNone
    For i = 1 To .Count
      If .Item(i) <> "" Then d1(.Item(i).Text) = ""
    Next
  End With
  With [A39:J1038]
    For i = 1 To .Count
      If d1.exists(.Item(i).Text) Then
        d2(.Item(i).Text) = ""
        .Item(i).ClearContents
      End If
    Next
  End With
  With [AF666:AO866]
    For i = 1 To .Count
      If d2.exists(.Item(i).Text) Then
        .Item(i).Interior.ColorIndex = 7
      End If
    Next
  End With
End Sub