|
求助高手帮忙加个判断,如果某整列没有数据就停止复制。
附件中,例如AB列为空,AB列后面所有的数据不需要被字典复制。代码如下。
Sub test()
Dim r%, i%, C, aa, ws
Dim arr, brr
Dim d As Object
Dim ColAry, qq As Long, fc As Long
Dim wwss As Worksheet
Application.ScreenUpdating = False
Application.DisplayAlerts = False
Worksheets("数据").Select
Set d = CreateObject("scripting.dictionary")
With Worksheets("数据")
Worksheets("数据").Select
r = .Cells(.Rows.Count, 1).End(xlUp).Row
C = .Cells(61, .Columns.Count).End(xlToLeft).Column
arr = .Range("g1:G" & r)
For i = 61 To UBound(arr)
If Len(arr(i, 1)) <> 0 Then
If Not d.exists(arr(i, 1)) Then
Set d(arr(i, 1)) = Range("a60").Resize(1, C)
End If
Set d(arr(i, 1)) = Union(d(arr(i, 1)), .Cells(i, 1).Resize(1, C))
End If
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("a60")
.[a60].CurrentRegion = .[a60].CurrentRegion.Value
End With
Next
For Each wwss In Worksheets
wwss.Columns("B:C").ColumnWidth = 6
Next wwss
Application.ScreenUpdating = True
ActiveWorkbook.RefreshAll
End Sub
|
|