设置打印区域在Excel 2013中使用宏 [英] Set printing area In Excel 2013 using macro

问题描述

在Excel 2013中，将表格命名为标签，我试图从 A2 设置打印区域 >到页尾，以 L 结尾。

In Excel 2013, having sheet named "Tags", I am trying to set a printing area from A2 till end of page, ending with 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?

推荐答案

如果您声明了几个变量并分解了您的陈述，那么更容易看出发生了什么。

It's easier to see what is happening if you declare a few variables and decompose your statement.

尝试这样：

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

或者，如果要查找包含数据的lastRow你可以找到这样的最后一个：

Alternatively, if you want to find the lastRow with data, you can find the lastrow like this:

lastRow = ws.Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row

请注意，您使用的65536值作为起点发现最后一行是过时的（尽管它将经常仍然可以工作），Excel 2007中每行有超过一百万行。

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.

需要注意的几件事情方法：

A few things to note about your approach:

1. 单元格（2,1）是A2。语法是单元格（[row]，[column]）


2. 您希望列L中的最后一个填充行，但正在查找在列A中。 Range（A65536）。End（xlUp）.Row

1. Cells(2,1) is A2. The syntax is Cells([row], [column])
2. You want the last populated row in column L, but are looking in column A instead. Range("A65536").End(xlUp).Row

导致打印区域（一旦添加了.Address到您的范围）A1：L2。为什么A1？因为列A为空，因此第1行为最后一行。您将范围设置为A2：L1，即A1：L2。

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.

这篇关于设置打印区域在Excel 2013中使用宏的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！