Excel宏：一列自动筛选打印，两列自动筛选打印

日期:2024-05-10 来源:本站整理点击:80

两列自动筛选打印
Sub 两列自动筛选打印()
   Dim slt_col
   Dim arr, brr, i As Long
   Dim rg As Range
   Dim objDic As Object

   Dim slt_col2
   Dim arr2, brr2, j As Long
   Dim rg2 As Range
   Dim objDic2 As Object

   Set slt_rng = Application.InputBox("请选第一拆分依据列！只能选择单列！", Title:="提示", Type:=8)
   slt_col = slt_rng.Column

   Set slt_rng2 = Application.InputBox("请选第二拆分依据列！只能选择单列！", Title:="提示", Type:=8)
   slt_col2 = slt_rng2.Column

   MsgBox " 准备按【" & ActiveSheet.Cells(1, slt_col) & " + " & ActiveSheet.Cells(1, slt_col2) & "】列进行分类筛选，并打印"

   Set rg = ActiveSheet.Range("a1").CurrentRegion
   arr = ActiveSheet.Range("a1").CurrentRegion
   Set objDic = CreateObject("scripting.dictionary")

       Set rg2 = ActiveSheet.Range("a1").CurrentRegion
       arr2 = ActiveSheet.Range("a1").CurrentRegion
       Set objDic2 = CreateObject("scripting.dictionary")

   '首先按第一拆分依据列进行筛选
   For i = 2 To UBound(arr)
       objDic(arr(i, slt_col)) = ""
   Next
   brr = objDic.keys
   For i = LBound(brr) To UBound(brr)
       rg.AutoFilter field:=slt_col, Criteria1:=brr(i) '其中的“slt_col”就是第一要筛选的列

       '然后再按第二拆分依据列进行筛选
       For j = 2 To UBound(arr2)
           objDic2(arr2(j, slt_col2)) = ""
       Next
       brr2 = objDic2.keys
       For j = LBound(brr2) To UBound(brr2)
           rg2.AutoFilter field:=slt_col2, Criteria1:=brr2(j) '其中的“slt_col2”就是第二要筛选的列
           ActiveSheet.PrintPreview
           'ActiveSheet.PrintOut
       Next j
       rg2.AutoFilter

   Next i
   rg.AutoFilter

MsgBox "【" & ActiveSheet.Cells(1, slt_col) & " + " & ActiveSheet.Cells(1, slt_col2) & "】列分类筛选打印，完成！"
End Sub

2 首页上一页 1 2

上一篇: 返回列表
下一篇: Excel退出时不提示保存的解决方法

相关文章

热门文章