vba 每 x 行数插入空行
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/14086620/
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
Inserting blank rows every x number of rows
提问by Max_1234
Possible Duplicate:
Insert row every X rows in excel
可能的重复：
在excel中每X行插入一行
I have a large set of data (let's say it goes from B5 to J500 and let's say this range is named rngOutput). I am trying to go through this data and add 2 empty rows every xnumber of rows where x is a number the user specifies. For example if x is 10 then every 10 rows 2 new rows should be inserted. Conceptually, this is the code that should work:
我有大量数据（假设它从 B5 到 J500，假设这个范围被命名为 rngOutput）。我正在尝试浏览这些数据，并每x行数添加 2 个空行，其中 x 是用户指定的数字。例如，如果 x 是 10，那么每 10 行应该插入 2 个新行。从概念上讲，这是应该工作的代码：
For i = 1 to Number of rows in rngOutput
If i mod x = 0 Then
Insert 2 Rows
End If
Next i
However, when you insert 2 new rows, the row count changes and the formula messes up (i.e. it adds 2 rows after the first 10 rows, then it adds another 2 rows after the next 8 rows (since it counts those 2 new rows you added as actual rows) then it adds another 2 rows after the next 6 rows, etc.
但是，当您插入 2 行新行时，行数会发生变化并且公式会混乱（即它在前 10 行之后添加 2 行，然后在接下来的 8 行之后添加另外 2 行（因为它计算了您添加为实际行）然后在接下来的 6 行之后添加另外 2 行，依此类推。
I am trying to figure out a way to accomplish adding 2 new rows every x number of rows cleanly to avoid the above problem.
我试图找出一种方法来完成每 x 行干净地添加 2 个新行以避免上述问题。
Thank you for the help and please let me know if you need additional clarification!
感谢您的帮助，如果您需要更多说明，请告诉我！
回答by Doug Glancy
This is like Chris's only fleshed out. When inserting or deleting rows you have to work up from the bottom:
这就像克里斯唯一的充实。插入或删除行时，您必须从底部开始：
Sub InsertXRowsEveryYRows_WithMeaningfulVariableNames()
Dim NumRowsToInsert As Long
Dim RowIncrement As Long
Dim ws As Excel.Worksheet
Dim LastRow As Long
Dim LastEvenlyDivisibleRow
Dim i As Long
NumRowsToInsert = 2 'any number greater than 0
RowIncrement = 10 'ditto
Set ws = ActiveSheet
With ws
LastRow = .Range("A" & .Rows.Count).End(xlUp).Row
LastEvenlyDivisibleRow = Int(LastRow / RowIncrement) * RowIncrement
If LastEvenlyDivisibleRow = 0 Then
Exit Sub
End If
Application.ScreenUpdating = False
For i = LastEvenlyDivisibleRow To 1 Step -RowIncrement
.Range(i & ":" & i + (NumRowsToInsert - 1)).Insert xlShiftDown
Next i
End With
Application.ScreenUpdating = True
End Sub
回答by chris neilsen
Count from the bottom of the range
从范围的底部开始计数
For i = Number of rows in rngOutput to 1 step -1
If i mod x = 0 Then
Insert 2 Rows
End If
Next i
