vba 在 Excel 宏中按索引号复制和粘贴行

Copy and Paste row by index number in Excel Macro

提问by Paul Etscheit

I'm trying to copy an entire row by index number and paste it to another row with a different index number when a certain condition is met (I know the issue is not with the conditional logic). I'm thinking of something like this:

我正在尝试按索引号复制整行，并在满足特定条件时将其粘贴到具有不同索引号的另一行（我知道问题不在于条件逻辑）。我在想这样的事情：

Sub Makro1()

Dim i As Integer

With ActiveSheet
    'for looping
    totalRows = .Cells(.Rows.Count, "A").End(xlUp).Row

    'index of last row even after rows have been added
    lastRow = .Cells(.Rows.Count, "A").End(xlUp).Row

    'data starts at row #3
    For i = 3 To totalRows
        If .Cells(i, 19).Value > 0 Then
            Number = .Cells(i, 19).Value
            Do While Number > 0
                lastRow = lasRow + 1
                'Next line doesnt do anything
                .Rows(lastRow) = .Rows(i).Value
                Number = Number - 1
            Loop
        End If
    Next i
End With
End Sub

The logic works like its supposed to but no lines are pasted. I've gone step by step and am certain the problem is not with the logic.

逻辑像它应该的那样工作，但没有粘贴任何行。我已经一步一步地走了，我确信问题不在于逻辑。

回答by Ajeet Shah

I assume that you want to copy Rows(i)and paste it as value in Rows(lastRow). So, you need to replace this line

我假设您想将其复制Rows(i)并粘贴为Rows(lastRow). 所以，你需要更换这一行

 .Rows(lastRow) = .Rows(i).Value

with these two lines:

用这两行：

.Rows(i).Copy
.Rows(lastRow).PasteSpecial xlPasteValues

Or

或者

.Rows(lastRow).Copy
.Rows(i).PasteSpecial xlPasteValues

if you want to copy Rows(lastRow)and paste it as value in Rows(i).

如果要将其复制Rows(lastRow)并粘贴为Rows(i).

Edit:

编辑：

To paste everything (formulas + values + formats), use paste type as xlPasteAll.

要粘贴所有内容（公式 + 值 + 格式），请使用粘贴类型为xlPasteAll.

Reference: msdn

参考：msdn

回答by Paul Etscheit

Range Copy and Paste

范围复制和粘贴

Syntax

句法

Range().Copy [Destination]

Range().Copy [目的地]

The square brackets indicate that Destination is an optional parameter. If you don't designate a Destination range it copies the selection to the clipboard. Otherwise it copies the first range directly to the new location.

方括号表示 Destination 是一个可选参数。如果您不指定目标范围，它会将选择复制到剪贴板。否则，它会将第一个范围直接复制到新位置。

Change this line:

改变这一行：

.Rows(lastRow) = .Rows(i).Value

.Rows(lastRow) = .Rows(i).Value

To:

到：

.Rows(lastRow).copy .Rows(i)

.Rows(lastRow).copy .Rows(i)

It's worth noting that

值得注意的是

.Rows(lastRow).copy .Cells(i, 1)

.Rows(lastRow).copy .Cells(i, 1)

Will also work. Excel will resize the Destination range to fit the new data.

也会工作。Excel 将调整目标范围的大小以适应新数据。

回答by user3598756

your code works for me

你的代码对我有用

so just add a breakpoint at .Rows(lastRow) = .Rows(i).Valuestatement and then query all relevant variables value in the Immediate Window, like:

所以只需在 . Rows(lastRow) = .Rows(i).Value语句，然后在立即窗口中查询所有相关变量的值，例如：

?lastRow
?.Rows(lastRow).Address
?i
?.Rows(i).Address

in the meanwhile you could

同时你可以

• add Option Explicitstatement at the very top of your code module

  this will force you to declare all variables and thus lead to some extra work, but you'll get repaid with much more control over your variables usage and misspelling, thus saving debugging time

• dim variables to hold rows index as of Longtype, to handle rows index higher then 32767

• avoid inner loop using the Resize()method of range object

• Option Explicit在代码模块的最顶部添加语句

  这将迫使您声明所有变量，从而导致一些额外的工作，但您将通过对变量使用和拼写错误的更多控制而获得回报，从而节省调试时间

• 将行索引保持为Long类型的变暗变量，以处理高于 32767 的行索引

• 使用Resize()范围对象的方法避免内循环

much like follows:

很像如下：

Option Explicit

Sub Makro1()

    Dim i As Long, totalRows As Long, lastRow As Long, Number As Long

    With ActiveSheet
        'for looping
        totalRows = .Cells(.Rows.Count, "A").End(xlUp).Row

        'index of row to add from
        lastRow = totalRows + 1 '<--| start pasting values one row below the last non empty one in column "A"

        'data starts at row #3
        For i = 3 To totalRows
            If .Cells(i, 19).Value > 0 Then
                Number = .Cells(i, 19).Value
                .Rows(lastRow).Resize(Number).Value = .Rows(i).Value
                lastRow = lastRow + Number
            End If
        Next i
    End With
End Sub