Sub Test()
Dim sh As Worksheet, d As Object
Dim i%, j&, jj&, x%
Dim arr1, arr2, k
Application.ScreenUpdating = False
Application.DisplayAlerts = False
'删除总表以外的所有工作表
For Each sh In Worksheets
If sh.Name <> Worksheets(1).Name Then
sh.Delete
End If
Next sh
'赋初值
Set d = CreateObject("Scripting.Dictionary")
arr1 = Range("b9").CurrentRegion '注意修改
arr2 = arr1
x = InputBox("按数据源的第几列分:", , 1)
'判断
If VBA.IsNumeric(x) = False Then
MsgBox "输入列号不合法,请重新输入!", 48, "出错了"
End
End If
'建立分表名称数组
For i = 2 To UBound(arr1, 1)
If Len(arr1(i, x)) Then d(arr1(i, x)) = ""
Next i
k = d.keys
'循环每个新表
For i = 0 To UBound(k)
Sheets.Add after:=Sheets(Sheets.Count)
ActiveSheet.Name = i 'k(i)
'循环每行
For j = 1 To UBound(arr1, 1)
'如果列值不是分表名称,则整行清零
If arr1(j, x) <> k(i) And j <> 1 Then
'第j行整行清零
For jj = 1 To UBound(arr1, 2)
arr1(j, jj) = ""
Next jj
End If
Next j
Range("a1").Resize(UBound(arr1, 1), UBound(arr1, 2)) = arr1
Range("a1:a" & UBound(arr1, 1)).SpecialCells(xlCellTypeBlanks).Delete (3)
'恢复arr1数据
arr1 = arr2
Next i
Sheets(1).Select
End Sub
筛选3.rar
(22.43 KB, 下载次数: 10)