|
[广告] Excel易用宝 - 提升Excel的操作效率 · Excel / WPS表格插件 ★ 免费下载 ★ ★ 使用帮助★
合并为一个过程啦!请参考
Sub test()
Dim r%, i%
Dim arr, brr
Dim d() As Object
With Sheet1
If .[a1] = "" Then Exit Sub
arr = .[a1].CurrentRegion.Value
Range("a1").Resize(1, UBound(arr, 2) + 50) = ""
Range("a1").Resize(1, UBound(arr, 2)) = arr
col = UBound(arr, 2)
ReDim d(1 To col)
For i = 1 To col
Set d(i) = CreateObject("scripting.dictionary")
Next
For j = 1 To UBound(arr, 2)
For i = 1 To UBound(arr)
d(j)(arr(i, j)) = ""
Next
Next
End With
With Sheet3
r = .Cells(.Rows.Count, 1).End(xlUp).Row
arr = .Range("a2:b" & r)
ReDim brr(1 To UBound(arr), 1 To col + 1)
For j = 1 To col
m = 0
For i = 1 To UBound(arr)
If Not d(j).exists(arr(i, 1)) Then
m = m + 1
brr(m, j) = arr(i, 2)
End If
Next
Next
For i = 1 To UBound(arr)
brr(i, col + 1) = Rnd
Next
End With
With Sheet2
hs = UBound(brr): ls = UBound(brr, 2)
.UsedRange.Offset(1, 0).ClearContents
.Range("a2").Resize(hs, ls) = brr
.Range(.Cells(2, 1), .Cells(hs, ls)).Sort key1:=.Cells(2, ls), order1:=xlAscending, Header:=2
.Columns(ls) = ""
arr = .UsedRange.Offset(1, 0)
r = UBound(arr, 1)
c = UBound(arr, 2)
ReDim brr(1 To r, 1 To c)
For j = 1 To UBound(arr, 2)
m = 0
For i = 1 To r
If Len(arr(i, j)) Then
m = m + 1
brr(m, j) = arr(i, j)
End If
Next
Next
.UsedRange.Offset(1, 0).ClearContents
.Range("a2").Resize(UBound(brr), UBound(brr, 2)) = brr
.[a1].Select
End With
End Sub |
评分
-
2
查看全部评分
-
|