每个循环的MS Excel：插入行

I have a worksheet containing 242 rows. I want to create a new row beneath each existing one. Instead, my code creates 242 rows below row 1. I have spent all afternoon on Google and Stack Overflow and tried various ideas but get the same problem. Here's my code:

我有一个包含242行的工作表。我想在每个现有的下面创建一个新行。相反，我的代码在第1行下面创建了242行。我整个下午都花在了Google和Stack Overflow上并尝试了各种想法，但却遇到了同样的问题。这是我的代码：

Function rws() As Integer

rws = (Cells(Rows.Count, "A").End(xlUp).Offset(1, 0).row)

End Function

Sub InsRws()

Dim rng As Range
Dim row As Range

Set rng = Range("A1:A" & rws - 1)

For Each row In rng.Rows
    Rows.Select
    ActiveCell.Offset(1).EntireRow.Insert
Next row

End Sub

3 个解决方案

#1

Maybe this will help

也许这会有所帮助

Function rws() As Integer

rws = (Cells(rows.Count, "A").End(xlUp).Offset(1, 0).row)

End Function

Sub InsRws()
    Dim rowCount As Integer
    Dim i As Integer
    rowCount = rws
    For i = 1 To rowCount
         Range("A" + CStr(2 * i - 1)).Select
         ActiveCell.Offset(1).EntireRow.Insert
    Next
End Sub

#2

Easiest way is to count up rather down down, like this

最简单的方法就是倒计时，就像这样

Sub InsertRows()
    Dim rw As Long

    With ActiveSheet
        For rw = .Cells(.Rows.Count, 1).End(xlUp).Row To 2 Step -1
            .Rows(rw).Insert
        Next
    End With
End Sub

#3

If you debug in for each loop debug.print row.address you will notice your rng keeps expanding when there is an insert. Instead you can use for loop as in below code.

如果为每个循环debug.print row.address调试，你会注意到当插入时你的rng不断扩展。相反，您可以使用for循环，如下面的代码。

Sub InsRws()

    Dim lastRow As Long
    lastRow = Sheets("Sheet1").Range("A65000").End(xlUp).Row

    For i = 1 To lastRow
        Range("A" & i * 2).EntireRow.Insert
    Next

End Sub

#1