设置打印区域的代码不起作用

I'm pretty new to VBA and Excel. I have a code that sets up the Print Area for a document

我是VBA和Excel的新手。我有一个代码,用于设置文档的打印区域

Dim count As Integer
count = ThisWorkbook.Worksheets("sheet2").Range("E106").Value

    Dim i As Integer
    i = count + 4


    Sheets("sheet1").PageSetup.PrintArea = Range(Cells(3, 2), Cells(35, i))
    End Sub

But when I go into Print Preview, the full sheet is displayed. Sheet1 and sheet2 are aliases for the worksheet names, but I have other code that references them and it works just fine. I already tried recording the Macro to set the print area in the first place, then inserted my custom range. Any help on what I'm doing wrong would be greatly appreciated :)

但是当我进入“打印预览”时,会显示完整的工作表。 Sheet1和sheet2是工作表名称的别名,但我有其他引用它们的代码,它工作得很好。我已经尝试录制宏来首先设置打印区域,然后插入我的自定义范围。对我所做错的任何帮助都将不胜感激:)

2 个解决方案

#1

The PrintArea Property of the PageSetup is not an object (range) but a string holding the address of the desired range. Try this:

PageSetup的PrintArea属性不是对象(范围),而是包含所需范围地址的字符串。试试这个:

With Sheets("sheet1")
    .PageSetup.PrintArea = .Range(.Cells(3, 2), .Cells(35, i)).Address
End With             '                                        ^^^^^^^^

#2

A bit more advanced version (tested):

更高级版本(已测试):

With ThisWorkbook.Worksheets("Sheet1")
    .PageSetup.PrintArea = .Range("offset(B3,0,0,32,Sheet2!E106+2)").Address
    .DisplayPageBreaks = False   ' optional to hide the print area rectangle
End With

#1