vba 在excel中每X行插入一行
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/5017181/
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
Insert row every X rows in excel
提问by dzilla
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 等，需要多行文本来解释它们。我有代码列表，我想知道如何自动每行插入一行，比如说，第五行。下面的例子
1
2
3
4
5
6
7
8
9
10...
100
Every five rows I would like to insert a given number of my choosing of rows. How can I do this? Thanks
每五行我想插入给定数量的我选择的行。我怎样才能做到这一点？谢谢
采纳答案by diagonalbatman
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
回答by Fionnuala
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
回答by diagonalbatman
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
回答by Thunderbuck
Here's the code I wound up with. Note that the FOR loop actually runs backwardsfrom the end of UsedRange. The Mod 5 inserts a row every 5 rows.
这是我结束的代码。请注意，FOR 循环实际上是从 UsedRange 的末尾向后运行的。Mod 5 每 5 行插入一行。
For i = ActiveSheet.UsedRange.Rows.Count To 2 Step -1
If (i - 1) Mod 5 = 0 Then
ActiveSheet.Rows(i).Insert Shift:=xlDown
End If
Next
回答by Dave
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.
您可以将其纳入安迪的回答中。
回答by mlunoe
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 nto get every n'th row.
注意：将 7 更改为n以获取每n行。
回答by Tiny24
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
