论坛 › 文本处理与Office › Excel 在不规则多行合并的多个单元格，自动插入分页符

plaodj 发表于 2018-4-7 22:16:49

Excel 在不规则多行合并的多个单元格，自动插入分页符

在excel中   不规则合并的单元格 自动加入分页符如图


效果如图


宏代码：

Sub l()
    Dim i As Integer, j As Integer
    With Sheet1
      i = ..End(3).Row
      .HPageBreaks.Add .Cells(i + 1, 1)
      .PageSetup.PrintArea = .Range("a1:h" & i).Address
      For j = 3 To i
            If Len(.Cells(j, 1)) Then .HPageBreaks.Add .Cells(j, 1)
      Next
    End With
End Sub

宏代码2：设置第一行为顶端标题。
Sub l()
    Dim i As Integer, j As Integer
    With Sheet1
      i = ..End(3).Row
      .HPageBreaks.Add .Cells(i + 1, 1)
      .PageSetup.PrintArea = .Range("a1:h" & i).Address
      .PageSetup.PrintTitleRows = "$1:$1"
      For j = 3 To i
            If Len(.Cells(j, 1)) Then .HPageBreaks.Add .Cells(j, 1)
      Next
    End With
End Sub

plaodj 发表于 2018-4-7 22:17:44

Option Explicit

Sub l()
    Dim i As Integer, j As Integer
    insert_h
    With Sheet1
      i = ..End(3).Row
      .HPageBreaks.Add .Cells(i + 1, 1)
      .PageSetup.PrintArea = .Range("a1:h" & i).Address
      .PageSetup.PrintTitleRows = "$1:$1"
      For j = 3 To i
            If Len(.Cells(j, 1)) Then .HPageBreaks.Add .Cells(j, 1)
      Next
    End With
End Sub
Sub insert_h()
    Dim i As Integer
    Dim j As Integer
    With Sheet1
      For i = ..End(3).Row To 3 Step -1
            If Len(.Cells(i, 1)) Then .Rows(i).Insert Shift:=xlDown
      Next
    End With
End Sub

plaodj 发表于 2018-4-7 22:18:58

Option Explicit
Sub l()
    Dim i As Integer, j As Integer
    insert_h
    With Sheet1
      i = ..End(3).Row
      .HPageBreaks.Add .Cells(i + 1, 1)
      .PageSetup.PrintArea = .Range("a1:h" & i).Address
      .PageSetup.PrintTitleRows = "$1:$1"
      For j = 3 To i
            If Len(.Cells(j, 1)) = 0 Then .HPageBreaks.Add .Cells(j + 1, 1)
      Next
    End With
End Sub
Sub insert_h()
    Dim i As Integer
    Dim j As Integer
    With Sheet1
      For i = ..End(3).Row To 3 Step -1
            If .Cells(i, 1) <> .Cells(i - 1, 1) Then .Rows(i).Insert Shift:=xlDown
      Next
    End With
End Sub

plaodj 发表于 2018-4-7 22:20:06

Sub l()
    Dim i As Integer, j As Integer
    insert_h
    With Sheet1
      i = ..End(3).Row
      .HPageBreaks.Add .Cells(i + 1, 1)
      .PageSetup.PrintArea = .Range("a1:h" & i).Address
      .PageSetup.PrintTitleRows = "$1:$1"
      For j = 3 To i
            If Len(.Cells(j, 1)) = 0 Then .HPageBreaks.Add .Cells(j + 1, 1)
      Next
    End With
End Sub

Option Explicit
Sub l()
    Dim i As Integer, j As Integer
    Dim rng As Range
    On Error Resume Next
    Set rng = Sheet1.Range("b:b").SpecialCells(xlCellTypeBlanks)
    If Not rng Is Nothing Then rng.EntireRow.Delete
    insert_h
    With Sheet1
      .Range("f:h").SpecialCells(xlCellTypeBlanks).FormulaR1C1 = "=SUMIF(C1,R[-1]C1,C)"
      i = ..End(3).Row
      .HPageBreaks.Add .Cells(i + 1, 1)
      .PageSetup.PrintArea = .Range("a1:h" & i).Address
      .PageSetup.PrintTitleRows = "$1:$1"
      For j = 3 To i
            If Len(.Cells(j, 1)) = 0 Then .HPageBreaks.Add .Cells(j + 1, 1)
      Next
    End With
End Sub
Sub insert_h()
    Dim i As Integer
    Dim j As Integer
    With Sheet1
      For i = ..End(3).Row To 3 Step -1
            If .Cells(i, 1) <> .Cells(i - 1, 1) Then .Rows(i).Insert Shift:=xlDown
      Next
    End With
End Sub

查看完整版本: Excel 在不规则多行合并的多个单元格，自动插入分页符