时间戳

首页 > 解决方案 > 在分页预览中自动插入分页符

excel - 在分页预览中自动插入分页符

问题描述

我有根据 C 列中的部分自动插入分页符的代码。

我的部分有 4 行。

在此处输入图像描述

这是有时在部分位于 B 列时可以工作的代码，现在部分在 C 列中，我已经更改了范围，但它似乎不起作用：

Dim fnd As Range, r As Range, pb As Variant
Dim PrintVersion As Worksheet

Set PrintVersion = ThisWorkbook.Sheets("Print version")

PrintVersion.Activate

   ' make sure sheet is in page break view
    PrintVersion.Parent.Windows(1).View = xlPageBreakPreview

    ' first clear any set page breaks
    On Error Resume Next
    For Each pb In PrintVersion.HPageBreaks
        pb.Delete
    Next
    On Error GoTo 0

    ' move preposed breaks to top of segement
    With PrintVersion.HPageBreaks
        For pb = 1 To .Count
            Set r = Cells(.Item(pb).Location.Row, 3)
            Set fnd = Range("C:C").Find("*", r, , , , xlPrevious)
            If Not Intersect(fnd.Offset(, -1).Resize(fnd.Offset(, 1).End(xlDown).Row - fnd.Row + 1, 4), r) Is Nothing Then
                Set .Item(pb).Location = fnd
            DoEvents
        End If
        Next
    End With

在此之前我有包装和自动装配：

With PrintVersion.Range("Print_Area")

        With .Cells.Rows
            .WrapText = True
            .VerticalAlignment = xlCenter
            .EntireRow.AutoFit
        End With
End With

结果（分页符应该在第 148 行）：

在此处输入图像描述

标签： excelvba

解决方案

我建议在第一列ResetAllPageBreaks中重置所有分页符：Find

Private Sub BreakPages()
    Dim fnd As Range, r As Range, pb As Variant
    Dim PrintVersion As Worksheet

    Set PrintVersion = ThisWorkbook.Sheets("Print version")

    PrintVersion.Activate

    ' make sure sheet is in page break view
    PrintVersion.Parent.Windows(1).View = xlPageBreakPreview

    ' first clear any set page breaks
    PrintVersion.ResetAllPageBreaks

    ' move preposed breaks to top of segement
    With PrintVersion.HPageBreaks
        For pb = 1 To .Count
            ' check if first column is empty
            Set r = PrintVersion.Cells(.Item(pb).Location.Row, 1)
            If r.value = "" Then
                ' find previous cell in column 1 which is not empty
                Set fnd = PrintVersion.Columns(1).Find("*", r, , , , xlPrevious)
                ' set page break 1 row above it
                Set .Item(pb).Location = fnd.Offset(-1, 0)
                DoEvents
            End If
        Next
    End With
End Sub

推荐阅读