具有动态范围的 VBA 复制和粘贴
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/12138624/
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 copy & paste with dynamic range
提问by cem karadas
I'm new with VBA and I'm stuck somewhere. I have to copy last row of column A till column H and paste it untill the last row of column I. Last rows of columnns will be always change.
我是 VBA 的新手，我被困在某个地方。我必须将 A 列的最后一行复制到 H 列并将其粘贴到 I 列的最后一行。 columnns 的最后一行将始终更改。
e.g; my data is in A2:H2 and I5 is the last cell with data.
My code should be copy A2:H2 and paste it A3:H5. And second time I run the macro (after I add new data to respective columns) it should be copy A6:H6 and paste it untill the last row of column I.
例如; 我的数据在 A2:H2 中，I5 是最后一个包含数据的单元格。
我的代码应该是复制 A2:H2 并将其粘贴到 A3:H5。第二次我运行宏（在我向各个列添加新数据之后）它应该是复制 A6:H6 并将其粘贴到 I 列的最后一行。
I wrote two codes which were not fulfill my needs.
我写了两个不能满足我需求的代码。
first code is;
第一个代码是；
Sub OrderList1()
Range("a65536").End(xlUp).Resize(1, 8).Copy _
(Cells(Cells(Rows.Count, 9).End(xlUp).Row, 1))
End Sub
this code skips A3:H4 and only pastes to A5:H5
此代码跳过 A3:H4 并仅粘贴到 A5:H5
second code is;
第二个代码是;
Sub OrderList2()
Range("A2:H2").Copy Range(Cells(2, 8), _
Cells(Cells(Rows.Count, 9).End(xlUp).Row, 1))
End Sub
it copies A2:H3 and paste it A5:H5 but when I add new data it doesn't start to paste from A5:H5. It start from A2:H2 and overwrite to old data. I can see what I have to change,range should be dynamic range like in the first code,but I can't manage to write the code.
它复制 A2:H3 并将其粘贴到 A5:H5，但是当我添加新数据时，它不会从 A5:H5 开始粘贴。它从 A2:H2 开始并覆盖旧数据。我可以看到我必须更改的内容，范围应该是第一个代码中的动态范围，但我无法编写代码。
I'll really appreciate little help.
我真的很感激一点帮助。
回答by JohnB
You might want to use this as a starting point:
您可能想以此为起点：
Dim columnI As Range
Set columnI = Range("I:I")
Dim columnA As Range
Set columnA = Range("A:A")
' find first row for which cell in column A is empty
Dim c As Range
Dim i As Long
i = 1
For Each c In columnA.Cells
If c.Value2 = "" Then Exit For
i = i + 1
Next c
' ok, we've found it, now we can refer to range from columns A to H of the previous row
' to a variable (in the previous row, column A has not been empty, so it's the row we want
' to copy)
Dim lastNonEmptyRow As Range
Set lastNonEmptyRow = Range(Cells(i - 1, 1), Cells(i - 1, 8))
' and now copy this range to all further lines, as long as columnI is not empty
Do While columnI(i) <> ""
lastNonEmptyRow.Copy Range(Cells(i, 1), Cells(i, 8))
i = i + 1
Loop
回答by Matt Ridge
Try this one for something that allows future functionality, or at least it did for me...Ask if you need help understanding it :)
试试这个可以实现未来功能的东西，或者至少它对我有用......询问你是否需要帮助理解它:)
Option Explicit
Sub lastrow()
Dim wsS1 As Worksheet 'Sheet1
Dim lastrow As Long
Dim lastrow2 As Long
Set wsS1 = Sheets("Sheet1")
With wsS1
'Last row in A
lastrow = Range("A" & Rows.Count).End(xlUp).Row
'Last Row in I
lastrow2 = Range("I" & Rows.Count).End(xlUp).Row
'Cut in A:H and paste into last row on I
wsS1.Range("A2:H" & lastrow).Cut wsS1.Range("I" & lastrow2)
End With
End Sub
