vba 使用宏在 Excel 2013 中设置打印区域
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/17623445/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Set printing area In Excel 2013 using macro
提问by ?mega
In Excel 2013, having sheet named "Tags", I am trying to set a printing areafrom A2till end of page, ending with column L.
在 Excel 2013 中，有一个名为"Tags" 的工作表，我试图设置一个打印区域，从A2页面到末尾，以 column 结尾L。
Worksheets("Tags").PageSetup.PrintArea = Worksheets("Tags").Range( _
Cells(2, 1), Cells(Worksheets("Tags").Range("A65536").End(xlUp).Row, 12))
My code compiles okay, but it does not seems to work - no printing area has been set.
我的代码编译正常，但似乎不起作用 - 没有设置打印区域。
What should be a correct macro to set printing area?
设置打印区域的正确宏应该是什么？
回答by Jon Crowell
It's easier to see what is happening if you declare a few variables and decompose your statement.
如果您声明一些变量并分解您的语句，则更容易看到发生了什么。
Try this:
尝试这个：
Sub SetPrintArea()
Dim ws As Worksheet
Dim lastRow As Long
Set ws = ThisWorkbook.Sheets("Tags")
' find the last row with formatting, to be included in print range
lastRow = ws.UsedRange.SpecialCells(xlCellTypeLastCell).Row
ws.PageSetup.PrintArea = ws.Range("A2:L" & lastRow).Address
End Sub
Alternatively, if you want to find the lastRow with data, you can find the lastrow like this:
或者，如果您想找到带有数据的 lastRow，您可以像这样找到 lastrow：
lastRow = ws.Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
Note that the 65536 value you're using as the starting point to find the last row is obsolete (although it will frequently still work) as of Excel 2007, which has over a million rows per sheet.
请注意，从 Excel 2007 开始，您用作查找最后一行的起点的 65536 值已过时（尽管它经常仍然有效），每个工作表有超过一百万行。
A few things to note about your approach:
关于你的方法有几点需要注意：
Cells(2,1)is A2. The syntax isCells([row], [column])- You want the last populated row in column L, but are looking in column A instead.
Range("A65536").End(xlUp).Row
Cells(2,1)是 A2。语法是Cells([row], [column])- 您想要 L 列中的最后一个填充行，但正在 A 列中查找。
Range("A65536").End(xlUp).Row
This results in a print area (once you've added the .Address to your range) of A1:L2. Why A1? Because the column A is empty, and the lastrow is therefore row 1. You have set the range to be A2:L1, which becomes A1:L2.
这会导致 A1:L2 的打印区域（一旦您将 .Address 添加到您的范围内）。为什么是A1？因为 A 列是空的，所以最后一行是第 1 行。您已将范围设置为 A2:L1，它变为 A1:L2。
回答by Andy G
You need to add .Addressat the end of your code.
您需要.Address在代码末尾添加。
Worksheets("Tags").PageSetup.PrintArea = Worksheets("Tags").Range( _
Cells(2, 1), Cells(Worksheets("Tags").Range("A65536").End(xlUp).Row, 12)).Address
PageSetup.PrintArea Property
PageSetup.PrintArea 属性
Returns or sets the range to be printed, as a string using A1-style references in the language of the macro. Read/write String.
返回或设置要打印的范围，作为使用宏语言中 A1 样式引用的字符串。读/写字符串。
