Increase the Maximum string length of Excel PrintArea

Question

I asked a question a while back about the Maximum string length of Excel Print Area :

Maximum String Length of PrintArea in Excel

Print Area is set at 255, for Excel 2010.

I think this is a read-only property but (clutching at straws here), is there any way to Increase the Print Area length? Excel plug-in maybe?

If not, I'll have to programmatically set a new print area, once the maximum 255 has been reached.

EDIT:

I've tried the answer below :

Range("A1:C3,A5:B6,C7:D8,E1:E2,G1:G2,A10:A11,A13:A14,A16:A17,A19:A20,A22:A23,A25:A26,A28:A29,A30:A31,A33:A35,A37:A38,A40:A41,A43:A44,A46:A47,A49:A50,A52:A53,A55:A56,A58:A59,A61:A63,A65:A66,A68:A69,A71:A72,A74:A75,A77:A78,A80:A81,A83:A84,A86:A87,A88:A89,A91:A92").Name = "MyNamedRange"

Works fine - 253 characters.

Range("A1:C3,A5:B6,C7:D8,E1:E2,G1:G2,A10:A11,A13:A14,A16:A17,A19:A20,A22:A23,A25:A26,A28:A29,A30:A31,A33:A35,A37:A38,A40:A41,A43:A44,A46:A47,A49:A50,A52:A53,A55:A56,A58:A59,A61:A63,A65:A66,A68:A69,A71:A72,A74:A75,A77:A78,A80:A81,A83:A84,A86:A87,A88:A89,A91:A92,A94:A95").Name = "MyNamedRange"

Run- Time error 1004 -(261 characters)

So, I guess now I should set multiple Named Ranges, then set the Active Sheet Print Area, using each named range in turn?

Thanks very much.

what is the reason you did not mark/accept the answer below? — Alex Gordon

SeanC SeanC · Accepted Answer · 2012-07-10T19:42:16

~~cheat with named ranges:~~

Range("A1:C3,A5:B6,C7:D8").Name = "MyNamedRange"
then
ActiveSheet.PageSetup.PrintArea = "MyNamedRange"

Seems there's a 255 character limitation on Named Ranges too.

next solution would be to hide the rows you don't want printed:

Private Sub Workbook_BeforePrint(Cancel As Boolean)
    If ActiveSheet.Name = "Sheet1" Then
        Cancel = True ' cancel default print method so we can print out stuff ourselves
        Application.EnableEvents = False
        Application.ScreenUpdating = False
        With ActiveSheet
            .Rows("12").EntireRow.Hidden = True ' hide the rows you want hidden
            .Rows("15").EntireRow.Hidden = True ' hide the rows you want hidden
            .PrintOut
            .Rows("12").EntireRow.Hidden = False ' unhide them after
            .Rows("15").EntireRow.Hidden = False
        End With
        Application.EnableEvents = True
        Application.ScreenUpdating = True
    End If
End Sub

Increase the Maximum string length of Excel PrintArea

2 Answers