在日常工作中,我们经常会遇到大量合并的单元格,而部分合并的单元格会跨页显示,也即:合并的单元格区域显示在两页中。如下图示:
而我们最终想要的打印效果,如下:
那么,上述效果是如何实现的呢?方法如下:
步骤1:按Alt+F11快捷键打开工程资源管理器窗口,即VBE窗口;
步骤2:依次单击菜单栏上的[插入]---[模块]选项,在模块1中输入以下代码:
Sub 完整显示合并的单元格区域()
Dim P, MergeAddress As String, PageCell As Range, MergeValue
ActiveWindow.View = xlPageBreakPreview
For Each P In ActiveSheet.HPageBreaks
Set PageCell = Cells(P.Location.Row - 1, ActiveCell.Column)
If PageCell.MergeCells And Not Intersect(Cells(P.Location.Row, ActiveCell.Column), PageCell.MergeArea) Is Nothing Then
MergeAddress = PageCell.MergeArea.Address
MergeValue = PageCell.MergeArea(1).Value
PageCell.MergeArea.UnMerge
Range(Range(MergeAddress)(1), PageCell).Merge
With Range(PageCell.Offset(1, 0), Cells(Split(MergeAddress, "$")(4), ActiveCell.Column))
.Merge
.Value = MergeValue
End With
End If
Next
ActiveWindow.View = xlNormalView
End Sub
模块1代码的截图如下:
步骤3:返回工作表界面,因B列是合并的区域,所以,我们首先选择B列数据,然后在模块1代码窗口中,按[执行]按钮,也即达到我们想要的效果。
鸣谢:若喜欢本篇文章,欢迎您的点赞,评论和分享!谢谢!!