VBA复制&动态范围粘贴 [英] VBA copy & paste with dynamic range

问题描述

我是 VBA 新手，但我被困在某个地方.我必须将 A 列的最后一行复制到 H 列并将其粘贴到 I 列的最后一行.列的最后一行将始终更改.

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.

例如;我的数据在 A2:H2 中，I5 是最后一个有数据的单元格.
我的代码应该是复制 A2:H2 并粘贴到 A3:H5.第二次运行宏(在我将新数据添加到各个列之后)它应该复制 A6:H6 并将其粘贴到 I 列的最后一行.

我写了两个不能满足我需求的代码.

I wrote two codes which were not fulfill my needs.

第一个代码是;

  Sub OrderList1()

    Range("a65536").End(xlUp).Resize(1, 8).Copy _
    (Cells(Cells(Rows.Count, 9).End(xlUp).Row, 1))

  End Sub

此代码跳过 A3:H4 并仅粘贴到 A5:H5

this code skips A3:H4 and only pastes to A5:H5

第二个代码是;

 Sub OrderList2()
   Range("A2:H2").Copy Range(Cells(2, 8), _
   Cells(Cells(Rows.Count, 9).End(xlUp).Row, 1))

 End Sub

它复制 A2:H3 并将其粘贴到 A5:H5，但是当我添加新数据时，它不会从 A5:H5 开始粘贴.它从 A2:H2 开始并覆盖旧数据.我可以看到我必须更改的内容，范围应该是第一个代码中的动态范围，但我无法编写代码.

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.

非常感谢您的帮助.

推荐答案

您可能希望以此为起点:

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

这篇关于VBA复制&动态范围粘贴的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！