Excel多区域大打印范围设置异常：仅打印前18页问题排查

我尝试在Excel中设置多个大范围打印区域，现有若干Excel文档，需要为每个指定区域设置打印范围。以下是我的代码：

Sub SetPrintArea()
    Dim ws As Worksheet
    Dim yRng1() As Variant
    Dim yRng2() As Variant
    Dim yRng3() As Variant
    Dim yRng4() As Variant
    Dim yRng5() As Variant
    Dim yRng6() As Variant
    Dim yRng7() As Variant
    Dim yRng8() As Variant
    Dim yRng9() As Variant
    Dim yRng10() As Variant
    Dim yRng11() As Variant
    Dim yRng12() As Variant
    Dim yRng13() As Variant
    Dim yRng14() As Variant
    Dim yRng15() As Variant
    Dim yRng16() As Variant
    Dim yRng17() As Variant
    Dim yRng18() As Variant
    Dim yRng19() As Variant
    Dim yRng20() As Variant
    Dim combinedArrays() As Variant
    Dim printRange As Range
    
    Set ws = ThisWorkbook.Sheets("Manifest")

    yRng1 = Array(ws.Range("A1:W59"), ws.Range("A62:W120"), ws.Range("A123:W181"), ws.Range("A184:W242"))
    yRng2 = Array(ws.Range("A245:W303"), ws.Range("A306:W364"), ws.Range("A367:W425"), ws.Range("A428:W486"), ws.Range("A489:W547"))
    yRng3 = Array(ws.Range("A550:W608"), ws.Range("A611:W669"), ws.Range("A672:W730"), ws.Range("A733:W791"), ws.Range("A794:W852"))
    yRng4 = Array(ws.Range("A855:W913"), ws.Range("A916:W974"), ws.Range("A977:W1035"), ws.Range("A1038:W1096"))
    yRng5 = Array(ws.Range("A1099:W1157"), ws.Range("A1160:W1218"), ws.Range("A1221:W1279"), ws.Range("A1282:W1340"), ws.Range("A1343:W1401"))
    yRng6 = Array(ws.Range("A1404:W1462"), ws.Range("A1465:W1523"), ws.Range("A1526:W1584"), ws.Range("A1587:W1645"), ws.Range("A1648:W1706"))
    yRng7 = Array(ws.Range("A1709:W1767"), ws.Range("A1770:W1828"), ws.Range("A1831:W1889"), ws.Range("A1892:W1950"), ws.Range("A1953:W2011"))
    yRng8 = Array(ws.Range("A2014:W2072"), ws.Range("A2075:W2133"), ws.Range("A2136:W2194"), ws.Range("A2197:W2255"), ws.Range("A2258:W2316"))
    yRng9 = Array(ws.Range("A2319:W2377"), ws.Range("A2380:W2438"), ws.Range("A2441:W2499"), ws.Range("A2502:W2560"), ws.Range("A2563:W2621"))
    yRng10 = Array(ws.Range("A2624:W2682"), ws.Range("A2685:W2743"), ws.Range("A2746:W2804"), ws.Range("A2807:W2865"), ws.Range("A2868:W2926"))
    yRng11 = Array(ws.Range("A2929:W2987"), ws.Range("A2990:W3048"), ws.Range("A3051:W3109"), ws.Range("A3112:W3170"), ws.Range("A3173:W3231"))
    yRng12 = Array(ws.Range("A3234:W3292"), ws.Range("A3295:W3353"), ws.Range("A3356:W3414"), ws.Range("A3417:W3475"), ws.Range("A3478:W3536"))
    yRng13 = Array(ws.Range("A3539:W3597"), ws.Range("A3600:W3658"), ws.Range("A3661:W3719"), ws.Range("A3722:W3780"), ws.Range("A3783:W3841"))
    yRng14 = Array(ws.Range("A3844:W3902"), ws.Range("A3905:W3963"), ws.Range("A3966:W4024"), ws.Range("A4027:W4085"), ws.Range("A4088:W4146"))
    yRng15 = Array(ws.Range("A4149:W4207"), ws.Range("A4210:W4268"), ws.Range("A4271:W4329"), ws.Range("A4332:W4390"), ws.Range("A4393:W4451"))
    yRng16 = Array(ws.Range("A4454:W4512"), ws.Range("A4515:W4573"), ws.Range("A4576:W4634"), ws.Range("A4637:W4695"), ws.Range("A4698:W4756"))
    yRng17 = Array(ws.Range("A4759:W4817"), ws.Range("A4820:W4878"), ws.Range("A4881:W4939"), ws.Range("A4942:W5000"), ws.Range("A5003:W5061"))
    yRng18 = Array(ws.Range("A5064:W5122"), ws.Range("A5125:W5183"), ws.Range("A5186:W5244"), ws.Range("A5247:W5305"), ws.Range("A5308:W5366"))
    yRng19 = Array(ws.Range("A5369:W5427"), ws.Range("A5430:W5488"), ws.Range("A5491:W5549"), ws.Range("A5552:W5610"), ws.Range("A5613:W5671"))
    yRng20 = Array(ws.Range("A5674:W5732"), ws.Range("A5735:W5793"), ws.Range("A5796:W5854"), ws.Range("A5857:W5915"), ws.Range("A5918:W5976"))
    
    combinedArrays = Array(yRng1, yRng2, yRng3, yRng4, yRng5, yRng6, yRng7, yRng8, yRng9, yRng10, yRng11, yRng12, yRng13, yRng14, yRng15, yRng16, yRng17, yRng18, yRng19, yRng20)


    Set printRange = combinedArrays(0)(0)

    For i = 0 To UBound(combinedArrays)
        For j = 0 To UBound(combinedArrays(i))
            If i = 0 And j = 0 Then
                Set printRange = combinedArrays(i)(j)
            Else
                Set printRange = Union(printRange, combinedArrays(i)(j))
            End If
        Next j
    Next i

    ws.PageSetup.printArea = printRange.Address

End Sub

但打印时仅得到18页，范围仅包含至“A1038:W1096”，我期望每个定义的区域各占一页。请问这是Excel的限制，还是我的代码存在问题？

问题根源 #

• Excel打印区域字符长度限制：Excel的PageSetup.PrintArea属性支持的地址字符串长度上限为255个字符。你通过Union合并大量非连续区域后，生成的地址字符串超过了这个限制，导致后续区域被自动截断，只保留了前18个区域的地址。
• Union合并的局限性：用Union合并大量分散区域时，地址字符串会快速膨胀，极易触发长度上限，无法完整保存所有目标区域。

解决方法 #

方法1：插入分页符并设置整体打印范围 #

要让每个定义区域各占一页，最可靠的方式是在每个区域末尾插入分页符，再将包含所有区域的整体范围设为打印区域：

Sub SetPrintAreaWithPageBreaks()
    Dim ws As Worksheet
    Dim currentRng As Range
    Dim i As Integer
    Dim yRngs As Variant
    
    Set ws = ThisWorkbook.Sheets("Manifest")
    ' 清除现有分页符
    ws.ResetAllPageBreaks
    
    ' 将所有目标区域合并为一维数组，简化遍历
    yRngs = Array( _
        ws.Range("A1:W59"), ws.Range("A62:W120"), ws.Range("A123:W181"), ws.Range("A184:W242"), _
        ws.Range("A245:W303"), ws.Range("A306:W364"), ws.Range("A367:W425"), ws.Range("A428:W486"), ws.Range("A489:W547"), _
        ws.Range("A550:W608"), ws.Range("A611:W669"), ws.Range("A672:W730"), ws.Range("A733:W791"), ws.Range("A794:W852"), _
        ws.Range("A855:W913"), ws.Range("A916:W974"), ws.Range("A977:W1035"), ws.Range("A1038:W1096"), _
        ws.Range("A1099:W1157"), ws.Range("A1160:W1218"), ws.Range("A1221:W1279"), ws.Range("A1282:W1340"), ws.Range("A1343:W1401"), _
        ws.Range("A1404:W1462"), ws.Range("A1465:W1523"), ws.Range("A1526:W1584"), ws.Range("A1587:W1645"), ws.Range("A1648:W1706"), _
        ws.Range("A1709:W1767"), ws.Range("A1770:W1828"), ws.Range("A1831:W1889"), ws.Range("A1892:W1950"), ws.Range("A1953:W2011"), _
        ws.Range("A2014:W2072"), ws.Range("A2075:W2133"), ws.Range("A2136:W2194"), ws.Range("A2197:W2255"), ws.Range("A2258:W2316"), _
        ws.Range("A2319:W2377"), ws.Range("A2380:W2438"), ws.Range("A2441:W2499"), ws.Range("A2502:W2560"), ws.Range("A2563:W2621"), _
        ws.Range("A2624:W2682"), ws.Range("A2685:W2743"), ws.Range("A2746:W2804"), ws.Range("A2807:W2865"), ws.Range("A2868:W2926"), _
        ws.Range("A2929:W2987"), ws.Range("A2990:W3048"), ws.Range("A3051:W3109"), ws.Range("A3112:W3170"), ws.Range("A3173:W3231"), _
        ws.Range("A3234:W3292"), ws.Range("A3295:W3353"), ws.Range("A3356:W3414"), ws.Range("A3417:W3475"), ws.Range("A3478:W3536"), _
        ws.Range("A3539:W3597"), ws.Range("A3600:W3658"), ws.Range("A3661:W3719"), ws.Range("A3722:W3780"), ws.Range("A3783:W3841"), _
        ws.Range("A3844:W3902"), ws.Range("A3905:W3963"), ws.Range("A3966:W4024"), ws.Range("A4027:W4085"), ws.Range("A4088:W4146"), _
        ws.Range("A4149:W4207"), ws.Range("A4210:W4268"), ws.Range("A4271:W4329"), ws.Range("A4332:W4390"), ws.Range("A4393:W4451"), _
        ws.Range("A4454:W4512"), ws.Range("A4515:W4573"), ws.Range("A4576:W4634"), ws.Range("A4637:W4695"), ws.Range("A4698:W4756"), _
        ws.Range("A4759:W4817"), ws.Range("A4820:W4878"), ws.Range("A4881:W4939"), ws.Range("A4942:W5000"), ws.Range("A5003:W5061"), _
        ws.Range("A5064:W5122"), ws.Range("A5125:W5183"), ws.Range("A5186:W5244"), ws.Range("A5247:W5305"), ws.Range("A5308:W5366"), _
        ws.Range("A5369:W5427"), ws.Range("A5430:W5488"), ws.Range("A5491:W5549"), ws.Range("A5552:W5610"), ws.Range("A5613:W5671"), _
        ws.Range("A5674:W5732"), ws.Range("A5735:W5793"), ws.Range("A5796:W5854"), ws.Range("A5857:W5915"), ws.Range("A5918:W5976") _
    )
    
    ' 在每个区域的下一行插入水平分页符
    For i = LBound(yRngs) To UBound(yRngs)
        Set currentRng = yRngs(i)
        ws.HPageBreaks.Add Before:=currentRng.Cells(currentRng.Rows.Count + 1, 1)
    Next i
    
    ' 设置打印范围为第一个区域到最后一个区域的整体范围
    ws.PageSetup.PrintArea = ws.Range(yRngs(LBound(yRngs)), yRngs(UBound(yRngs))).Address
End Sub