在excel中每X行插入一行

I have a long list of codes such as 008.45, etc that will need multiple lines of text to explain them. I have the list of codes and I would like to know how I can automatically insert a row every, say, fifth row. Example Below

我有一长串的代码，如008.45等，需要多行文字来解释。我有代码列表，我想知道如何能自动插入每行，比如说，第五行。在下面的例子

Every five rows I would like to insert a given number of my choosing of rows. How can I do this? Thanks

每5行，我都要插入一个给定的行数。我该怎么做呢?谢谢

6 个解决方案

#1

You would need to use a loop as below:

您需要使用如下循环:

for i=1 to 100 step 1
  if i mod 5 = 0 then
     // Insert the rows
  end if
next i

#2

Test with a range from row 1 to row 100.

测试范围从第1行到第100行。

Sub InsertRows()
For i = Sheet1.UsedRange.Rows.Count To 1 Step -5
    For j = 0 To 4
        Sheet1.Rows(i).Insert
    Next
Next
End Sub

#3

This worked great for me:

这对我很有用:

Sub add_rows_n()

t = 6
Do Until Cells(t, "A") = ""
Rows(t).Insert
t = t + 6
Loop

End Sub

#4

To insert a row at row myRowNumber, your VBA code would look like this:

要在行myRowNumber上插入一行，您的VBA代码应该如下所示:

    Rows(myRowNumber).Select
    Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove

You can incorporate that into Andy's answer.

你可以把它融入安迪的回答中。

#5

Or you could use the modulus function like so:

或者可以用模函数，比如

=IF(MOD(ROW()-1,7),"",A1)

in B1, where A1 is the first number of your dataset.

在B1中，A1是数据集的第一个数字。

NB: Change 7 to n to get every n'th row.

从7到n，得到第n行。

#6

For example if I want 5 of my records between my rows of data I would use Mod 6, however, you need to allow for these new rows as they will affect the used range count! To do this you will want to add the number of rows that will be inserted to the length of the loop (eg. Absolute value of(numberOfRows/YourModValue)).

例如，如果我想要我的数据行之间的5个记录，我将使用Mod 6，但是，您需要允许这些新的行，因为它们将影响使用的范围计数!为此，您需要添加将插入到循环长度中的行数(例如)。的绝对值(numberOfRows / YourModValue))。

Code to do this:

代码:

Sub InsertRows()
For i = 1 To Sheet1.UsedRange.Rows.Count + Abs(Sheet1.UsedRange.Rows.Count / 6) Step 1
    If i Mod 6 = 0 Then
        Sheet1.Rows(i).Insert
        Cells(i, 1).Value = "Whatever data you want in your new separator cell"
    End If
Next i
End Sub

#1