我的目标:让中国的大学生走出校门的那一刻就已经具备这些office技能,让职场人士能高效使用office为其服务。支持鹏哥,也为自己加油!
下面是一位群友付费请教的问题(感谢愿为知识付费的同学),这里分享给大家:
![b782ba95de3342c3c5db8f0613673406.png](https://i-blog.csdnimg.cn/blog_migrate/9720e1f408dec779af85d4dad6c7d2b1.jpeg)
![72301fd8da2587a23126080a0dedb427.png](https://i-blog.csdnimg.cn/blog_migrate/5a8da6f38ebfd569542228159399c932.jpeg)
![d974ce850f9ef8b02b486da99e6d7ef2.gif](https://i-blog.csdnimg.cn/blog_migrate/82ccc41914f25ef9e5e8a976fa24e7fd.gif)
Sub 筛选数据() Dim sh As Worksheet Dim arr, d As Object, i As Long Dim row1 As Long, col1 As Long, arr1(), n1 As Long, n2 As Long, str$ Dim arr2, n3 As Long, c1$, c2$, arr3, arr4, arr5, n4 As Long, n5 As Long Dim pah$ '关闭系统提示 Application.DisplayAlerts = False '把数据区域读取到arr中 arr = Sheets(1).[a1].CurrentRegion '创建一个字典d,把A列和C列不重复的筛选条件合并写入到字典中 Set d = CreateObject("scripting.dictionary") For i = 1 To UBound(arr) d(arr(i, 1) & "," & arr(i, 3)) = "" Next '把数据区域每一行连接在一起写入数组arr1中,以便在其中进行筛选 row1 = UBound(arr) col1 = UBound(arr, 2) ReDim arr1(1 To row1) For n1 = 1 To row1 For n2 = 1 To col1 str = str & "," & arr(n1, n2) Next n2 arr1(n1) = Right(str, Len(str) - 1) str = "" Next n1 '拆分筛选条件并在arr1中筛选出符合条件的记录 arr2 = d.keys For n3 = 0 To d.Count - 1 c1 = Split(arr2(n3), ",")(0) c2 = Split(arr2(n3), ",")(1) arr3 = Filter(arr1, c1) arr4 = Filter(arr3, c2) '把符合条件的记录放到新工作簿中 ReDim arr5(0 To UBound(arr4), 0 To UBound(arr, 2) - 1) For n4 = 0 To UBound(arr4) For n5 = 0 To UBound(arr, 2) - 1 arr5(n4, n5) = Split(arr4(n4), ",")(n5) Next n5 Next n4 pah = ThisWorkbook.Path Workbooks.Add.SaveAs pah & "\" & Split(arr2(n3), ",")(0) & "+" & Split(arr2(n3), ",")(1) ActiveSheet.[a1].Resize(UBound(arr5) + 1, UBound(arr5, 2) + 1) = arr5 ActiveWorkbook.Close 1 '清空arr3 , arr4, arr5以备下次装入数据 Erase arr3 Erase arr4 Erase arr5 Next n3 '释放字典对象 Set d = Nothing '打开系统提示 Application.DisplayAlerts = True '激活数据源表 Sheets(1).Activate End Sub
向右滑动可以查看完整代码。
代码中每段都要提示,懂VBA基础的同学应该能看懂。
本节的分享就到这里,祝大家每天都有进步。
1
在线课堂在逐渐完善中,欢迎您的光临!
点击下方“”了解更多VBA的知识!