vba 如何将选定的范围（作为 rng 传递）粘贴到工作表的末尾？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/9404654/
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
How to paste the selected range (passed as rng) to the end of the worksheet?
提问by bsr
The code below is trying to paste the selected range (passed as rng) to the end of the worksheet.
下面的代码试图将选定的范围（作为 传递rng）粘贴到工作表的末尾。
It works if there are two rows already present (A1, A2).
如果已经存在两行（A1，A2），它会起作用。
Sub copyRow(rng As Range, ws As Worksheet)
Dim newRange As Range
Set newRange = ws.Range("A1").End(xlDown).Offset(1, 0)
rng.Copy
newRange.PasteSpecial (xlPasteAll)
End Sub
If A1 and A2 are present and you call this method 100 times, it inserts 100 rows after them.
如果 A1 和 A2 存在并且您调用此方法 100 次，它会在它们之后插入 100 行。
If no rows are present or only A1, it just overwrites A2. I could see Excel write on the same row (overwrite).
如果没有行或只有 A1，它只会覆盖 A2。我可以看到 Excel 写在同一行（覆盖）。
Appears something to do with how xlDown calculates if there are less than 2 rows.
如果少于 2 行，似乎与 xlDown 的计算方式有关。
回答by Tony Dallimore
Sorry but I do not agree with Michael's answer.
抱歉，我不同意迈克尔的回答。
End(xlDown) is the VBA equivalent of clicking Ctrl+Down.
End(xlDown) 相当于单击Ctrl+的 VBA Down。
Try Ctrl+Downwith
尝试Ctrl+Down与
- an empty column
- a column with a value in row 1 but no other
- values in rows 1 and 2
- values in rows 1, 2, 3, 7, 8, 9, 13, 14 and 15
- 空列
- 第 1 行有值但没有其他值的列
- 第 1 行和第 2 行中的值
- 第 1、2、3、7、8、9、13、14 和 15 行中的值
This will give you an idea of all the different rows, Ctrl+Downmight take you to.
这将使您了解所有不同的行，Ctrl+Down可能会带您到。
Set newRange = ws.Range("A1").End(xlDown).End(xlDown).End(xlUp).Offset(1, 0)does not necessarily take you to the last used row plus 1.
Set newRange = ws.Range("A1").End(xlDown).End(xlDown).End(xlUp).Offset(1, 0)不一定会将您带到最后使用的行加 1。
I am surprised Set newRange = ws.Range("A1").End(xlDown).Offset(1, 0)worked with an empty column. Range("A1").End(xlDown)would take you to the bottom row of the sheet then .Offset(1, 0)would try to take you off the sheet.
我很惊讶Set newRange = ws.Range("A1").End(xlDown).Offset(1, 0)使用空列。Range("A1").End(xlDown)将带您到工作表的底行，然后.Offset(1, 0)尝试将您从工作表上移开。
Consider:
考虑：
Dim RowLast As Long
RowLast = ws.Cells(Rows.Count, "A").End(xlUp).Row
- If column A is empty, RowLast will be set to 1.
- If A1 has a value but no other cells have values, RowLast will be set to 1.
- If a number of cells in column A have values, RowLast will be set to the bottom row with a value.
- If you have a value in the final row, it will be ignored.
- If you have a value in the final two rows, RowLast will be set to Rows.Count - 1.
- 如果 A 列为空，则 RowLast 将设置为 1。
- 如果 A1 有值但没有其他单元格有值，则 RowLast 将设置为 1。
- 如果 A 列中的多个单元格具有值，则 RowLast 将设置为带有值的底行。
- 如果您在最后一行中有一个值，它将被忽略。
- 如果您在最后两行中有一个值，则 RowLast 将设置为 Rows.Count - 1。
I assume you do not have values in the borrom rows. If you do not care if row 1 is left blank with an empty column, then:
我假设您在 borrom 行中没有值。如果您不关心第 1 行是否留有空白列，则：
RowLast = ws.Cells(Rows.Count, "A").End(xlUp).Row
Set NewRange = ws.Cells(RowLast + 1, "A")
should give the desired result regardless of the current contents of sheet ws.
无论工作表 ws 的当前内容如何，都应该给出所需的结果。
If you do care about row 1 being left blank, experimenting with Ctrl+Downand Ctrl+Upwill give you an understanding of the effect of different combinations of values.
如果您确实关心第 1 行是否留空，则尝试使用Ctrl+Down和Ctrl+Up将使您了解不同值组合的效果。
