|
楼主 |
发表于 2019-3-7 18:54
|
显示全部楼层
本帖最后由 hanzhang98 于 2019-3-8 09:09 编辑
附件怎么传不上去,先添加代码如下。
- Dim rCurrentCell As Range ' 每一页之分页小计所在单元格
- Dim r1stSubCell As Range ' 小计区域第一个单元格
- ' -------------------------------------------------
- ' 从这里开始执行
- Sub Main()
- t = Timer
- Application.ScreenUpdating = False '关闭屏幕刷新
- Worksheets("sheet1").Activate '激活工作表“sheet1”
- 删除分页符
- Set r1stSubCell = Range("A5") ' 本例名单从 A5 单元格开始
- For Each rCurrentCell In Range(r1stSubCell, r1stSubCell.End(xlDown))
- If rCurrentCell = "本页小计" Or rCurrentCell = "总 计" Then rCurrentCell.EntireRow.Delete
- Next
- Set r1stSubCell = Range("A5") ' 本例名单从 A5 单元格开始
- For Each rCurrentCell In Range(r1stSubCell, r1stSubCell.End(xlDown))
- If rCurrentCell = "本页小计" Or rCurrentCell = "总 计" Then rCurrentCell.EntireRow.Delete
- Next
- 新建分页小计
- Range("A5").Activate
- MsgBox "分页小计已完成!" & Chr(13) & "共用时间" & Round(Timer - t, 2) & "秒"
- Application.ScreenUpdating = True '开启屏幕刷新
- End Sub
- ' -------------------------------------------------
- Sub 删除原有的分页小计行()
- Application.ScreenUpdating = False '关闭屏幕刷新
- Set r1stSubCell = Range("A5") '本例名单从 A5 单元格开始
- For Each rCurrentCell In Range(r1stSubCell, r1stSubCell.End(xlDown))
- If rCurrentCell = "本页小计" Or rCurrentCell = "总 计" Then rCurrentCell.EntireRow.Delete
- Next
- Set r1stSubCell = Range("A5") ' 本例名单从 A5 单元格开始
- For Each rCurrentCell In Range(r1stSubCell, r1stSubCell.End(xlDown))
- If rCurrentCell = "本页小计" Or rCurrentCell = "总 计" Then rCurrentCell.EntireRow.Delete
- Next
- Range("A1").Activate
- MsgBox "已成功删除分页小计"
- Application.ScreenUpdating = True '开启屏幕刷新
- End Sub
- Sub 新建分页小计()
- Dim iSubCol As Integer, rSubArea As Range
- Dim hb As HPageBreak
- Worksheets("sheet1").Activate '激活工作表“sheet1”
- Rows(Range("a65536").End(xlUp).Row).Copy Range("a" & Range("a65536").End(xlUp).Row + 1)
- Rows(Range("a65536").End(xlUp).Row).ClearContents
- Range("a" & Range("a65536").End(xlUp).Row + 1) = " "
- ActiveWindow.View = xlPageBreakPreview ' 进入 分页浏览 模式, 以便 EXCEL 正确计页
- Set r1stSubCell = Range("A5") ' 本例名单从 A5 单元格开始
- iSubCol = 9 ' 本例小计项共有 21 列
- ' 最后一行插入手工分页符
- ActiveSheet.HPageBreaks.Add Before:=r1stSubCell.End(xlDown).Offset(1, 0)
- ActiveSheet.HPageBreaks.Add Before:=Range("a65536").End(xlUp)
- ' 测试每一个分页符,
- ' 如果是自动分页符, 则在其上一行插入一小计行, 而本行纳入下一页
- ' 否则, 在本行插入一小计行
- For Each hb In ActiveSheet.HPageBreaks
- Set rCurrentCell = hb.Location
- rCurrentCell.Select ' 看看先
- If hb.Type = xlPageBreakAutomatic Then Set rCurrentCell = rCurrentCell.Offset(-1, 0)
- rCurrentCell.EntireRow.Insert
- Set rCurrentCell = rCurrentCell.Offset(-1, 0)
- ' 添加分页小计内容
- With rCurrentCell
- .Value = "本页小计"
- .Font.Bold = True
- 'Set rSubArea = Application.Union(Range("d" & rCurrentCell.Row), Range("V" & rCurrentCell.Row)) ' 需要填充分页小计公式的区域V列
- 'Set rSubArea = Range("b" & rCurrentCell.Row, "V" & rCurrentCell.Row) ' 需要填充分页小计公式的区域V列''B至V列的区域(共21列)
- r = rCurrentCell.Row
- Set rSubArea = Union(Range("d" & r), Range("f" & r), Range("j" & r), Range("m" & r)) ''填充分页小计公式的区域仅是其中3列(f、i、j)
- ' 使用 SUBTOTAL 公式的好处是方便扩展, 且不会对已计算区域重复计算(如果可能发生这种情况的话)
- rSubArea.Formula = "=SUBTOTAL(9," & r1stSubCell.Offset(0, 5).Address(1, 0) & ":" & .Offset(-1, 5).Address(1, 0) & ")"
- Set r1stSubCell = .Offset(1, 0)
- End With
- Next
- Rows(Range("a65536").End(xlUp).Row).Clear
- If Range("A65536").End(xlUp) = " " Then Rows(Range("a65536").End(xlUp).Row).Clear
- Rows(Range("a65536").End(xlUp).Row).Copy Range("a" & Range("a65536").End(xlUp).Row + 1)
- Rows(Range("a65536").End(xlUp).Row).ClearContents
- With Range("A" & Range("A65536").End(xlUp).Row + 1)
- .Value = "总 计"
- .Font.Bold = True
- End With
- 'Set rSubArea = Range("b" & Range("A65536").End(xlUp).Row, "V" & Range("A65536").End(xlUp).Row) 'V列
- r = Range("A65536").End(xlUp).Row
- Set rSubArea = Union(Range("d" & r), Range("f" & r), Range("j" & r), Range("m" & r)) ''其中3列(f\i\j)
- rSubArea.Offset(0, 0) = "=SUBTOTAL(9," & "f5:f" & Range("A65536").End(xlUp).Row - 1 & ")"
- 删除分页符
- ActiveWindow.View = xlNormalView
- End Sub
- Sub 删除分页符()
- On Error Resume Next
- t = ActiveSheet.HPageBreaks.Count
- For n = t To 1 Step -1
- If ActiveSheet.HPageBreaks(n).Extent = xlPageBreakFull Then
- ActiveSheet.HPageBreaks(n).Delete
- End If
- t = ActiveSheet.HPageBreaks.Count
- Next
- End Sub
复制代码 |
|