Excel VBA 将打印区域设置为包含数据的最后一行
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/40576289/
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
Excel VBA set print area to last row with data
提问by Antoon
I have an Excel table with a sheet "CR" where columns A to K are filled, with the first row being a header row.
我有一个 Excel 表格，其中包含一张“CR”表，其中填充了 A 到 K 列，第一行是标题行。
Rows 1-1000 are formatted (borders) and column A contains a formula to autonumber the rows when data in column F is entered.
第 1-1000 行已格式化（边框），A 列包含一个公式，用于在输入 F 列中的数据时自动编号行。
Sheet "CR" is protected to prevent users from entering data in column A (locked).
表“CR”受到保护，以防止用户在 A 列（锁定）中输入数据。
Using the Workbook_BeforePrint function, I'm trying to set the print area to columns A to K and to the last row of column A that contains a number.
使用 Workbook_BeforePrint 函数，我试图将打印区域设置为 A 到 K 列以及包含数字的 A 列的最后一行。
My code (in object 'ThisWorkbook') is as follows:
我的代码（在对象“ThisWorkbook”中）如下：
Private Sub Workbook_BeforePrint(Cancel As Boolean)
Dim ws As Worksheet
Dim lastRow As Long
Set ws = ThisWorkbook.Sheets("CR")
' find the last row with data in column A
lastRow = .Cells(.Rows.Count, 1).End(xlUp).Row
ws.PageSetup.PrintArea = ws.Range("A1:K" & lastRow).Address
End Sub
However, when I click File -> Print, the range of columns A to K up to row 1000 is displayed instead of only the rows that have a number in column A. What am I doing wrong?
但是，当我单击“文件”->“打印”时，将显示 A 列到 K 列直到第 1000 行的范围，而不是仅显示 A 列中有数字的行。我做错了什么？
采纳答案by David
Change:
改变：
lastRow = .Cells(.Rows.Count, 1).End(xlUp).Row
To:
到：
lastRow = [LOOKUP(2,1/(A1:A65536<>""),ROW(A1:A65536))]
回答by Dirk Reichel
.End(...)will act like ctrl + arrow-key. if the cell has a formula (which looks empty due to the formula, then it will still stop there... another way would be the use of evaluate (if you do not want to loop) like this:
.End(...)就像 ctrl + 箭头键一样。如果单元格有一个公式（由于公式而看起来是空的，那么它仍然会停在那里……另一种方法是使用评估（如果您不想循环），如下所示：
lastRow = .Evaluate("MAX(IFERROR(MATCH(1E+100,A:A,1),0),IFERROR(MATCH(""zzz"",A:A,1),0))")
This will give the last row (which has a value in column A).
这将给出最后一行（在 A 列中有一个值）。
Also check if there are hidden values (looking empty due number format or having characters you can't see directly. Try to go below row 1000 in column A (select a cell after A1000 in column A) and hit ctrl+up to validate where it stops (and why).
还要检查是否有隐藏值（看起来是空的到期数字格式或有无法直接看到的字符。尝试在 A 列中的第 1000 行下方（在 A 列中选择 A1000 之后的单元格）并按 ctrl+up 以验证位置它停止（以及为什么）。
EDIT:
(regarding your comment)
编辑：（
关于你的评论）
""still leads to a "stop" for the .End(...)-command. So either use my formula, translate the formula into vba or loop the cells it get the last value. Also Findis a good tool (if not doing it over and over for countless times).
""仍然会导致.End(...)-command的“停止” 。因此，要么使用我的公式，将公式转换为 vba，要么循环获取最后一个值的单元格。也是Find一个很好的工具（如果不是一遍又一遍地做无数次的话）。
lastRow = .Cells.Find("*", .Range("B1"), xlValues, , xlByColumns, xlPrevious).Row
