|
[广告] VBA代码宝 - VBA编程加强工具 · VBA代码随查随用 · 内置多项VBA编程加强工具 ★ 免费下载 ★ ★ 使用手册★
增加一条判断如果某工作表不在A列的名称里面,则清空此工作表的内容
Sub test()
Set d = CreateObject("scripting.dictionary")
Set dnm = CreateObject("scripting.dictionary")
Application.ScreenUpdating = False
For Each sh In Sheets
dnm(sh.Name) = ""
Next sh
Sheets("罚款汇总明细").Select
arr = Sheets("罚款汇总明细").UsedRange
For j = 2 To UBound(arr)
If Len(arr(j, 1)) > 0 Then
If d.exists(arr(j, 1)) Then
Set d(arr(j, 1)) = Union(d(arr(j, 1)), Cells(j, 1))
Else
Set d(arr(j, 1)) = Union(Cells(1, 1), Cells(j, 1))
End If
End If
Next j
For j = 0 To d.Count - 1
str1 = d.keys()(j)
If dnm.exists(str1) Then
Sheets(str1).UsedRange.Clear
Else
Sheets.Add after:=Sheets(Sheets.Count)
Sheets(Sheets.Count).Name = str1
End If
d.items()(j).EntireRow.Copy Sheets(str1).[a1]
Next j
Application.ScreenUpdating = True
End Sub
表内有说明,谢谢
|
|