vba Excel 宏 - 动态设置打印区域

Excel Macro - Dynamically Set Print Area

提问by Vin

I have a table with a fixed number of columns, but the number of rows vary week on week.

我有一个列数固定的表，但行数每周都在变化。

Is there a macro I can create to set the print area automatically of this table?

我可以创建一个宏来自动设置该表的打印区域吗？

回答by Stephen Collins

I would combine a dynamically resizing named range with a VBA method.

我会将动态调整大小的命名范围与 VBA 方法结合起来。

First create a named range, MyNamedRange: (Assuming your table begins at $A$1 and your table has headers)

首先创建一个命名范围，MyNamedRange：（假设你的表从 $A$1 开始并且你的表有标题）

=OFFSET(A1,0,0,COUNTA(A:A)-1,COUNTA(1:1))

Then just execute a line of VBA:

然后只需执行一行VBA：

ActiveSheet.PageSetup.PrintArea = "MyNamedRange"

回答by syedkollol

Just use this simple code:

只需使用这个简单的代码：

 Private Sub prnt()
 On Error Resume Next

  Cells(1, 1).Select
  With ActiveSheet.PageSetup
    .PrintArea = Range(ActiveCell, ActiveCell.SpecialCells(xlCellTypeLastCell)).Select.Address
    .Orientation = xlLandscape
    .LeftHeader = "&p/&N"
    .LeftFooter = ActiveWorkbook.FullName 'to show address
    .PrintTitleRows = ":" 'repeat at top
    .Zoom = False
    .Orientation = xlLandscape
    .FitToPagesWide = 1 'to print in 01 page
    .FitToPagesTall = False 'to print in 01 page
  End With
End Sub

回答by syedkollol

I guess this is a pretty late response... The solution above didn't work for me in Excel 2007 so I used

我想这是一个很晚的回应......上面的解决方案在 Excel 2007 中对我不起作用，所以我使用了

begin_column = 1
end_column = 5

begin_row = 1
end_row = 30

'converting the numbers to uppercase letters
temp_begin_column = Chr(first_column + 64)
temp_end_column = Chr(second_column + 64)

ActiveSheet.PageSetup.PrintArea = "$" & temp_begin_column & "$" & begin_row & ":$" & temp_end_column & "$" & end_row

This may seem like a complicated solution but its the only thing that reliably worked for me

这似乎是一个复杂的解决方案，但它是唯一对我有用的可靠方法

回答by Bhanu Pratap

I have tried this and it Worked for me.

我试过这个，它对我有用。

StartColumn="A"
StartRow=1
EndColumn="B"
EndRow=10

ActiveSheet.PageSetup.PrintArea = StartColumn & StartRow & ":" & EndColumn & EndRow
ActiveSheet.PageSetup.PrintArea = "A1:B10"

Or 
ActiveSheet.PageSetup.PrintArea = "$" & StartColumn & "$" & StartRow & ":" & "$"  & EndColumn & "$" & EndRow
ActiveSheet.PageSetup.PrintArea = "$A:$B"

回答by acontrario

ActiveSheet.PageSetup.PrintArea = "MyNamedRange" as proposed above by Stephen Collins does not work for me.

ActiveSheet.PageSetup.PrintArea = "MyNamedRange" 上面由斯蒂芬柯林斯提出的对我不起作用。

But if I typed a slightly modified version: ActiveSheet.PageSetup.PrintArea = MyNamedRange.Address then it works perfectly in my context.

但是如果我输入一个稍微修改过的版本：ActiveSheet.PageSetup.PrintArea = MyNamedRange.Address 那么它在我的上下文中完美地工作。

I had Application.ReferenceStyle = xlR1C1 activated and not xlA1.

我激活了 Application.ReferenceStyle = xlR1C1 而不是 xlA1。

Note: ActiveSheet.PageSetup.PrintArea = MyNamedRange.Address(ReferenceStyle:=xlR1C1) would not work for me.

注意：ActiveSheet.PageSetup.PrintArea = MyNamedRange.Address(ReferenceStyle:=xlR1C1) 对我不起作用。

Similarly, ActiveSheet.PageSetup.PrintArea = StartColumn & StartRow & ":" & EndColumn & EndRow as proposed above by Bhanu Pratap works indeed very well, 1st time. Bu not so easy to manage programmatically (column letters).

同样，Bhanu Pratap 上面提出的 ActiveSheet.PageSetup.PrintArea = StartColumn & StartRow & ":" & EndColumn & EndRow 确实很好用，第一次。但是以编程方式管理并不那么容易（列字母）。

But using "R" & StartRow & "C" & StartColumn & ":" & "R" & EndRow & "C" & EndColumn --- does not work for me either. So, consistent.

但是使用 "R" & StartRow & "C" & StartColumn & ":" & "R" & EndRow & "C" & EndColumn --- 对我也不起作用。所以，一致。

Looking at https://docs.microsoft.com/en-us/office/vba/api/excel.pagesetup.printareait states that "you use the Address property to return an A1-style address."

查看https://docs.microsoft.com/en-us/office/vba/api/excel.pagesetup.printarea它指出“您使用 Address 属性返回 A1 样式的地址。”

So, it seems to be an expected VBA behaviour not to use xlR1C1 while it would be much easier to use programmatically.

因此，不使用 xlR1C1 似乎是预期的 VBA 行为，而以编程方式使用它会更容易。

My simple way around it:

我的简单方法：

Set MyNamedRange = Worksheets(i_sheet_idx).Range(Cells(StartRow, StartColumn), Cells(EndRow, EndColumn)) -- using the same variables as suggested above by Bhanu Pratap.

设置 MyNamedRange = Worksheets(i_sheet_idx).Range(Cells(StartRow, StartColumn), Cells(EndRow, EndColumn)) -- 使用 Bhanu Pratap 上面建议的相同变量。

Then ActiveSheet.PageSetup.PrintArea = MyNameRange.Address ' which does the job for me.

然后 ActiveSheet.PageSetup.PrintArea = MyNameRange.Address ' 为我完成这项工作。

So, I can programmatically play with the start/End Row/Columns easily. Using offset as suggested above should work also to change the range but this is independent from the programmatic difficulty encountered here in VBA to specify the range address in a way VBA would accept to swallow without error. I would not want to count the strange & unclear VBA errors I had on these trials. I don't use VBA often & never program otherwise (hence the struggle above). The goal was to print automatically, smartly & recurrently a large number of parts of a large worksheet following a pattern.

因此，我可以轻松地以编程方式播放开始/结束行/列。使用上面建议的偏移量也应该可以改变范围，但这与在 VBA 中遇到的编程困难无关，以 VBA 接受的方式指定范围地址而不会出错。我不想计算我在这些试验中遇到的奇怪和不清楚的 VBA 错误。我不经常使用 VBA 并且从不编程（因此上面的斗争）。目标是按照模式自动、巧妙地重复打印大型工作表的大量部分。

NB: possibly unrelated, I encountered in the debugging phase - just on the PageSetup.PrintArea line - as above a strange phenomenon where even if no error (so the code following later after rerun a fully expected & controlled path), my code would jump - sometimes - to a totally different sub or function in another workbook without reason (I have another personal workbook storing a number of work macros in several modules). It happens 4 times in tests. I tried to find events that could trigger this but could not find one. Sometimes it was the same sub/function being triggered, sometimes it was a different one, with no logical connection. But I noted that I had seen the same function being triggered in another situation before (see its basic code below), without good reason. So, something must happen at application level. In this "short piece of code" just written to test the above, I introduced later an error handler to catch err.number in case a problem would occur but of course, it did not reoccur.

注意：可能无关，我在调试阶段遇到了 - 只是在 PageSetup.PrintArea 行上 - 如上所述，即使没有错误（因此在重新运行完全预期和受控路径后的代码），我的代码也会跳转- 有时 - 无缘无故地在另一个工作簿中使用完全不同的子或函数（我有另一个个人工作簿，在多个模块中存储了许多工作宏）。它在测试中发生了 4 次。我试图找到可以触发此但找不到的事件。有时是相同的子/功能被触发，有时是不同的，没有逻辑联系。但我注意到我之前曾在另一种情况下看到相同的函数被触发（请参阅下面的基本代码），但没有充分的理由。因此，必须在应用程序级别发生某些事情。在这个“

I suppose that closing & restarting Excel (2013 here) should fix this error. This has happened to me before once in Excel 2010. A pointer going nuts but with some insistence, a repeated folly which supposes some logic behind. Weird.
Here is the function most often triggered in another module in another workbook (while not being programmatically activated at all, I repeat): it does not make logical sense to me but so it is:

我想关闭并重新启动 Excel（此处为 2013）应该可以解决此错误。这在 Excel 2010 中曾经发生在我身上。一个指针发疯了，但有一些坚持，一个重复的愚蠢，它假设背后有一些逻辑。奇怪的。
这是在另一个工作簿的另一个模块中最常触发的功能（虽然根本没有以编程方式激活，我再说一遍）：它对我来说没有逻辑意义，但它是：

Function HLink(rng As Range) As String 'extract URL from hyperlink If rng(1).Hyperlinks.Count Then HLink = rng.Hyperlinks(1).Address End Function

Function HLink(rng As Range) As String '从超链接中提取 URL If rng(1).Hyperlinks.Count Then HLink = rng.Hyperlinks(1).Address End Function

The other sub being activated did not make more apparent sense.

另一个被激活的潜艇没有更明显的意义。

回答by Chris Rae

If you're wanting to print the whole sheet always, you can actually just clear the print area and it will default to the amount of the sheet that is filled with data. If you're wanting not to hide some columns, wtfsven's answer is perfect.

如果您想始终打印整张工作表，您实际上可以只清除打印区域，它会默认为填充数据的工作表数量。如果您不想隐藏某些列，wtfsven 的答案是完美的。