如何将所选范围(作为rng传递)粘贴到工作表的末尾? [英] How to paste the selected range (passed as rng) to the end of the worksheet?

问题描述

下面的代码试图将所选范围(作为rng传递)粘贴到工作表的末尾.

The code below is trying to paste the selected range (passed as rng) to the end of the worksheet.

如果已经存在两行(A1，A2)，它将起作用.

It works if there are two rows already present (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

如果存在A1和A2，并且您调用此方法100次，它将在它们之后插入100行.

If A1 and A2 are present and you call this method 100 times, it inserts 100 rows after them.

如果不存在任何行或仅存在A1，则仅覆盖A2.我可以看到Excel在同一行上写(覆盖).

If no rows are present or only A1, it just overwrites A2. I could see Excel write on the same row (overwrite).

似乎与xlDown如何计算少于2行有关.

Appears something to do with how xlDown calculates if there are less than 2 rows.

推荐答案

对不起，但我不同意迈克尔的回答.

Sorry but I do not agree with Michael's answer.

End(xlDown)是单击Ctrl + Down的VBA等效项.

End(xlDown) is the VBA equivalent of clicking Ctrl+Down.

尝试Ctrl + Down与

• 一个空列
• 在第1行中有一个值但没有其他值的列
• 第1行和第2行中的值
第1、2、3、7、8、9、13、14和15行中的
• 值

• 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

这将使您了解所有不同的行，Ctrl + Down可能会将您带到.

This will give you an idea of all the different rows, Ctrl+Down might take you to.

Set newRange = ws.Range("A1").End(xlDown).End(xlDown).End(xlUp).Offset(1, 0)不一定带您到最后使用的行加1.

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).Offset(1, 0)使用一个空列. Range("A1").End(xlDown)会将您带到工作表的底行，然后.Offset(1, 0)会尝试将您带离工作表.

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.

考虑:

Dim RowLast As Long

RowLast = ws.Cells(Rows.Count, "A").End(xlUp).Row

• 如果列A为空，则RowLast将设置为1.
• 如果A1有一个值，但没有其他单元格有值，则RowLast将设置为1.
• 如果A列中的多个单元格都有值，则RowLast将被设置为带有值的最底行.
• 如果最后一行有一个值，它将被忽略.
• 如果最后两行都有值，则RowLast将设置为Rows.Count-1.
• 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.

我假设您在borrom行中没有值.如果您不关心第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:

RowLast = ws.Cells(Rows.Count, "A").End(xlUp).Row
Set NewRange = ws.Cells(RowLast + 1, "A")

无论表ws的当前内容如何，​​都应给出期望的结果.

should give the desired result regardless of the current contents of sheet ws.

如果您确实希望将第1行留空，则尝试使用Ctrl + Down和Ctrl + Up可以使您了解值的不同组合的影响.

If you do care about row 1 being left blank, experimenting with Ctrl+Down and Ctrl+Up will give you an understanding of the effect of different combinations of values.

这篇关于如何将所选范围(作为rng传递)粘贴到工作表的末尾?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！