外部数据模块,未设置自动刷新. 每次手动刷新后会自动覆盖,只好想到在工作表2中采取引用匹配的方式实现数据累积. 数据比较大,长时间开机搜集. 工作表2的指定区域已经标出,希望能过滤掉重复数据,以B列为参考. 还请能人,高手,大神多多帮忙. 拜托拜托! 谢谢咯.
本帖最后由 chart888 于 2017-6-20 22:49 编辑
- Private Sub CommandButton1_Click()
- Dim arr, brr
- Dim i&, j&, r&
- Application.ScreenUpdating = False
- On Error Resume Next
- Set D = CreateObject("Scripting.Dictionary")
- With Sheets("工作表2")
- arr = .Range("B2:B" & .Cells(Rows.Count, 2).End(3).Row)
- brr = Sheets("工作表1").Range("B2:G" & Sheets("工作表1").Cells(Rows.Count, 2).End(3).Row)
- For i = 1 To UBound(arr)
- D(arr(i, 1)) = i + 1
- Next
- r = UBound(arr) + 1
- For j = 1 To UBound(brr)
- If D.exists(brr(j, 1)) = False Then
- r = r + 1
- .Cells(r, 2) = brr(j, 1): .Cells(r, 3) = brr(j, 2)
- .Cells(r, 4) = brr(j, 3): .Cells(r, 5) = brr(j, 4)
- .Cells(r, 6) = brr(j, 5): .Cells(r, 7) = brr(j, 6)
- End If
- Next
- End With
- Application.ScreenUpdating = True
- End Sub
复制代码
|