another approach
Sub Test()
Dim pn As Range
Dim cn As Range
Dim i As Long, lr As Long, r As Long
With ActiveSheet
On Error Resume Next
lr = Cells.Find(What:="*", After:=.Cells(1, 1), LookIn:=xlValues, SearchOrder:=xlByRows, SearchDirection:=xlPrevious, SearchFormat:=False).Row
On Error GoTo 0
If lr = 0 Then Exit Sub
For i = lr To 2 Step -1
If .Range("a" & i) = "" Then .Range("a" & i).EntireRow.Delete
Next
On Error Resume Next
lr = .Cells.Find(What:="*", After:=.Cells(1, 1), LookIn:=xlValues, SearchOrder:=xlByRows, SearchDirection:=xlPrevious, SearchFormat:=False).Row
On Error GoTo 0
If lr > 0 Then
.ResetAllPageBreaks
With .PageSetup
.PrintArea = "A1:M" & lr
.Zoom = False
.FitToPagesWide = 1
.FitToPagesTall = False
End With
For r = 5 To lr
If .Range("A" & r).MergeArea.Cells.Count = 1 Then
Set cn = .Range("C" & r)
If Not pn Is Nothing Then
If cn.Value <> pn.Value Then
.HPageBreaks.Add before:=pn.Offset(1, 0)
End If
End If
Set pn = cn
End If
Next
End If
End With
End Sub
Bookmarks