|
[广告] Excel易用宝 - 提升Excel的操作效率 · Excel / WPS表格插件 ★ 免费下载 ★ ★ 使用帮助★
请问CurrentRegion.value 复制数字前面有零的时候,有没有办法把全部数字复制?有时前面的零
会被遗漏掉,有时确会一起复制过去。
附上代码。
Sub test()
Dim r%, i%, c, aa, ws
Dim arr, brr
Dim d As Object
Application.ScreenUpdating = False
Application.DisplayAlerts = False
Set d = CreateObject("scripting.dictionary")
With Worksheets("数据")
r = .Cells(.Rows.Count, 1).End(xlUp).Row
c = .Cells(1, .Columns.Count).End(xlToLeft).Column
arr = .Range("a1:a" & r)
For i = 2 To UBound(arr)
If Not d.exists(arr(i, 1)) Then
Set d(arr(i, 1)) = Range("a1").Resize(1, c)
End If
Set d(arr(i, 1)) = Union(d(arr(i, 1)), .Cells(i, 1).Resize(1, c))
Next
End With
For Each aa In d.keys
On Error Resume Next
Set ws = Worksheets(aa)
If Err Then
Set ws = Worksheets.Add(after:=Worksheets(Worksheets.Count))
ws.Name = aa
End If
On Error GoTo 0
With ws
d(aa).Copy .Range("a1")
.[a1].CurrentRegion = .[a1].CurrentRegion.Value
.[a1].CurrentRegion.WrapText = False
End With
Next
Application.ScreenUpdating = True
End Sub
|
|