有没有一种方法可以根据列值的更改在Excel中使用VBA插入分页符，但从页眉后开始

Question

我试过下面两种。两者都能很好地工作，只是因为所选列中的值从页眉值变为第一个实际值，所以会导致页面只有页眉。

选项1:

Dim I As Long, J As Long
J = ws.Cells(Rows.Count, "b").End(xlUp).Row
For I = J To 2 Step -1
If Range("b" & I).Value <> Range("b" & I - 1).Value Then
ActiveSheet.HPageBreaks.Add Before:=Range("b" & I)
End If
Next I 

选项2:

Dim rangeSelection As Range
Dim cellCurrent As Range
Set rangeSelection = Application.Selection.Columns(2).Cells
ActiveSheet.ResetAllPageBreaks
For Each cellCurrent In rangeSelection
If (cellCurrent.Row > 2) Then
If (cellCurrent.Value <> cellCurrent.Offset(-1, 0).Value) Then
ActiveSheet.Rows(cellCurrent.Row).PageBreak = _
xlPageBreakManual
End If
End If
Next cellCurrent

Answer 1

选项1(假设ws已设置(

Dim I As Long, J As Long
J = ws.Cells(ws.Rows.Count, "b").End(xlUp).Row
For I = J To 2 Step -1
If ws.Range("b" & I).Value <> ws.Range("b" & I - 1).Value Then
ws.HPageBreaks.Add Before:=ws.Range("b" & I)
End If
Next I 

选项2(假设ws设置为选项1(

Dim rangeSelection As Range
Dim cellCurrent As Range
Set rangeSelection = ws.Range(ws.Cells(1, 2), ws.Cells(ws.Rows.Count, 2).End(xlUp))
ws.ResetAllPageBreaks
For Each cellCurrent In rangeSelection
If cellCurrent.Row > 2 Then
If cellCurrent.Value <> cellCurrent.Offset(-1, 0).Value Then
ws.Rows(cellCurrent.Row).PageBreak = xlPageBreakManual
End If
End If
Next cellCurrent