|
- Sub 筛选()
- '
- ' 筛选 宏
- '
- Application.ScreenUpdating = False
- '
- With Range("A6:I" & [a65536].End(xlUp).Row)
- .Value = ""
- .Borders.LineStyle = xlNone '清除单元格边框线
- End With
- '先对总表以 月字段,升序进行排序,
- With Worksheets("总表")
- Dim iRow&
- iRow = .[a65536].End(xlUp).Row
- .Range("a1:i" & iRow).Sort _
- Key1:=.Range("c2:c" & iRow), Order1:=1, Header:=xlYes
- '高级筛选
- .Range("A1:I" & iRow).AdvancedFilter Action:=xlFilterCopy, _
- CriteriaRange:=Range("K1:K2"), CopyToRange:=Range("A5:I5")
- End With
- iRow = [a65536].End(xlUp).Row
- Range("a5:i" & iRow).Sort Key1:=Range("c6:i" & iRow), Order1:=xlAscending, _
- key2:=Range("a6:a" & iRow), order2:=xlAscending, Header:=xlYes, SortMethod:=xlPinYin
- With Range("C6")
- .Value = ""
- .Subtotal GroupBy:=3, Function:=xlSum, TotalList:=Array(7, 8), _
- Replace:=True, PageBreaks:=False, SummaryBelowData:=True
- .ClearOutline
- End With
- Dim i As Integer
- iRow = [h65536].End(xlUp).Row
- For i = iRow To 5 Step -1
- 'For i = [h65536].End(xlUp).Row To 5 Step -1
- If Cells(i, 3) = "总计" Then Rows(i).Delete
- If Cells(i, 3) Like "*汇总" Then Cells(i, 6) = "本期合计"
- If Cells(i, 3) Like "*汇总" Then Cells(i, 3).Value = ""
- If Cells(i, 6) = "本期合计" Then
- Rows(i + 1).Insert
- Cells(i + 1, 6) = "期初余额"
- End If
- Next i
- '正常位置
- iRow = [f65536].End(xlUp).Row
- Range("F" & iRow).Delete '删除最后一行数据
- Range("I7").FormulaR1C1 = "=IF(RC[-8]="""",R[-1]C,R[-1]C+RC[-2]-RC[-1])" '输入余额栏公式
- Range("I7").AutoFill Destination:=Range("I7:I" & iRow - 1), Type:=xlFillDefault
- Range("g6:I" & iRow - 1).NumberFormatLocal = "0.00_ ;[红色]-0.00 " '设置格式为两位小数
- With Range("F6:I" & iRow - 1) '.Select '设置摘要栏为自动缩小字体适应单元格
- .VerticalAlignment = xlCenter
- .ReadingOrder = xlContext
- End With
- With Columns("A:E") '.Select 'A到E列的内容居中
- .HorizontalAlignment = xlCenter
- .VerticalAlignment = xlCenter
- .ReadingOrder = xlContext
- End With
- Columns("E:E").NumberFormatLocal = "0000" '设置E列格式
- With Range("A5:I" & [I65536].End(xlUp).Row) '加边框线
- With .Borders
- .LineStyle = xlContinuous
- .Weight = xlThin
- End With
- End With
- With Range("B3") '设置B3和I3单元格格式
- .HorizontalAlignment = xlLeft
- .VerticalAlignment = xlCenter
- .ReadingOrder = xlContext
- End With
- With Range("I3")
- .HorizontalAlignment = xlRight
- .VerticalAlignment = xlCenter
- .ReadingOrder = xlContext
- End With
- Application.ScreenUpdating = True
- ActiveSheet.PageSetup.PrintArea = "A1:I" & iRow - 1 '设置打印区域
- End Sub
复制代码 重新整理了下,当是学下SORT |
|