VBA 帮助在动态范围内选择整行
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/19369812/
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
VBA help in selecting entire rows on a dynamic range
提问by James Chen
Hi thanks everyone in advance. I have a data set. lets say A3 to Z30. the number of rows and columns varies. also there are blanks in the set. So lets say i want to select the entire section but there's a blank in Z29 and X30 using
嗨，提前谢谢大家。我有一个数据集。让我们说 A3 到 Z30。行数和列数各不相同。集合中也有空白。所以假设我想选择整个部分，但在 Z29 和 X30 中有一个空白使用
Range("A3").Select
Range(Selection, Selection.End(xlDown)).Select
Range(Selection, Selection.End(xlToRight)).Select
Selection.Copy
isn't going to work.
不会工作。
But the values in column A is continuous. So i think the first part will work
但是 A 列中的值是连续的。所以我认为第一部分会起作用
Range("A3").Select
Range(Selection, Selection.End(xlDown)).Select
now i know this may seem elementry but how the heck do i select all the rows i just highlighted? this needs to be dynamic because as i said the number of columns and rows vary.
现在我知道这可能看起来很基本，但我到底如何选择我刚刚突出显示的所有行？这需要是动态的，因为正如我所说，列数和行数各不相同。
Oh and bonus karma and kudos if you can help me to figure out the next part. I need to select the range and paste it immediately after the the last row but the value in the first cell or A31 in this case would need to change and that is being pulled from a list in sheet2
哦，如果你能帮我弄清楚下一部分，那就是额外的业力和荣誉。我需要选择范围并在最后一行之后立即粘贴它，但在这种情况下第一个单元格或 A31 中的值需要更改，并且正在从 sheet2 中的列表中提取
回答by Lopsided
Use the .EntireRowmethod.
使用.EntireRow方法。
Here is an example:
下面是一个例子：
Dim report as Worksheet
Set report = Excel.ActiveSheet
report.cells(1,1).EntireRow.Select
If you want to select the cells themselves, you can use the .UsedRangemethod.
如果要选择单元格本身，可以使用该.UsedRange方法。
Here is an example:
下面是一个例子：
Dim report As Worksheet
Set report = Excel.ActiveSheet
report.Range(report.Cells(1, 1), report.Cells(1, report.UsedRange.Columns.Count)).Select
EDIT
编辑
Here is an example for part II of your question (as requested):
以下是您问题第二部分的示例（根据要求）：
Sub test2()
Dim report As Worksheet
Set report = Excel.ActiveSheet
report.Cells(1, 1).EntireRow.Copy
report.Cells(report.UsedRange.Rows.Count + 1, 1).EntireRow.PasteSpecial xlPasteAll
End Sub
Be sure to note that the .UsedRangemethod also includes cells that have no values but have been formatted by the user; e.g., if you add bold font (even if you don't add the text itself) to a cell in row 1000, your .UsedRange.Rows.Countwill be 1000.
请务必注意，该.UsedRange方法还包括没有值但已被用户格式化的单元格；例如，如果您将粗体字体（即使您不添加文本本身）添加到第 1000 行的单元格中，您.UsedRange.Rows.Count将是 1000。
Additionally, you can check my answer in the following link for more guidance. I've been told the notes are very helpful for beginners:
此外，您可以在以下链接中查看我的回答以获取更多指导。有人告诉我，这些笔记对初学者非常有帮助：
回答by Monty Wild
You might want to look at this, and consider what you could do with Range.CurrentRegion, Range.Resizeand Range.Offset, so you might get:
你可能想看看这个，并考虑你可以用做什么Range.CurrentRegion，Range.Resize和Range.Offset，所以你可能会得到：
Range("A3").CurrentRegion.Copy
Additionally, there's no need to use Range.Selectunless you want a user to see what is happening; instead of (for example) a Range.Selectfollowed by a Selection.Copy()(which copies to the clipboard), you could just use a Range.Copy(Range), which copies direct to the target range.
此外，Range.Select除非您希望用户看到正在发生的事情，否则无需使用；而不是（例如） aRange.Select后跟 a Selection.Copy()（复制到剪贴板），您可以只使用 a Range.Copy(Range)，它直接复制到目标范围。
As to the second part, you could:
至于第二部分，你可以：
Dim CopyRow as Long
CopyRow = Range("A3").CurrentRegion.Rows.Count
Range("A3").CurrentRegion.Copy(Range("A3").CurrentRegion.Offset(CopyRow))
Range("A3").Offset(CopyRow) = x ' Insert your reference to the Sheet 2 value here
回答by Edibled
I am aware that this thread is old, however I was looking for help with something similar. I knew my starting cell for my range, but the number of rows and columns were going to be dynamic. Using the following code worked for me:
我知道这个线程很旧，但是我正在寻找类似的帮助。我知道我的范围的起始单元格，但行数和列数将是动态的。使用以下代码对我有用：
Range("A2").Select
Range(Selection, Selection.End(xlDown).End(xlToRight)).Select
Hopefully anyone in the future can make use of this simple solution.
希望未来的任何人都可以使用这个简单的解决方案。
