Macro to insert every N row
- Thread starter bloubul
- Start date
Frank
Member
Well to save time I googled this one. There are a number of sites coming up with solutions. The problem is you have not specified:-
a) the range over which you want the macro to act, just a few rows or the whole sheet, and which few rows exactly ?
b) the interval (N)
c) how many rows to insert at each interval ?
This site came up with one which might suit you as it asks you each time questions a) , b) and c) before it runs.
https://www.extendoffice.com/documents/excel/2993-excel-insert-rows-at-intervals.html
The macro as presented did not actually work because some variables had not been defined. Below is my modified version of their macro.
Option Explicit
Sub InsertRowsAtIntervals()
'Updateby20150707
Dim Rng As Range
Dim xTitleId As String
Dim xInterval As Integer
Dim xRows As Integer
Dim xRowsCount As Integer
Dim xNum1 As Integer
Dim xNum2 As Integer
Dim i As Long
Dim WorkRng As Range
Dim xWs As Worksheet
xTitleId = "Select Range"
Set WorkRng = Application.Selection
Set WorkRng = Application.InputBox("Range", xTitleId, WorkRng.Address, Type:=8)
xRowsCount = WorkRng.Rows.Count
xInterval = Application.InputBox("Enter row interval. ", xTitleId, 1, Type:=1)
xRows = Application.InputBox("How many rows to insert at each interval? ", xTitleId, 1, Type:=1)
xNum1 = WorkRng.Row + xInterval
xNum2 = xRows + xInterval
Set xWs = WorkRng.Parent
For i = 1 To Int(xRowsCount / xInterval)
xWs.Range(xWs.Cells(xNum1, WorkRng.Column), xWs.Cells(xNum1 + xRows - 1, WorkRng.Column)).Select
Application.Selection.EntireRow.Insert
xNum1 = xNum1 + xNum2
Next
End Sub
Ive tested it and it seems to work, but you only need select a column to specify which rows you need the macro to act over.
If its not what you want, there are others out there, some of which have the interval fixed, or may auto detect the range to work over. It depends what you want. let me know if you have problems.
a) the range over which you want the macro to act, just a few rows or the whole sheet, and which few rows exactly ?
b) the interval (N)
c) how many rows to insert at each interval ?
This site came up with one which might suit you as it asks you each time questions a) , b) and c) before it runs.
https://www.extendoffice.com/documents/excel/2993-excel-insert-rows-at-intervals.html
The macro as presented did not actually work because some variables had not been defined. Below is my modified version of their macro.
Option Explicit
Sub InsertRowsAtIntervals()
'Updateby20150707
Dim Rng As Range
Dim xTitleId As String
Dim xInterval As Integer
Dim xRows As Integer
Dim xRowsCount As Integer
Dim xNum1 As Integer
Dim xNum2 As Integer
Dim i As Long
Dim WorkRng As Range
Dim xWs As Worksheet
xTitleId = "Select Range"
Set WorkRng = Application.Selection
Set WorkRng = Application.InputBox("Range", xTitleId, WorkRng.Address, Type:=8)
xRowsCount = WorkRng.Rows.Count
xInterval = Application.InputBox("Enter row interval. ", xTitleId, 1, Type:=1)
xRows = Application.InputBox("How many rows to insert at each interval? ", xTitleId, 1, Type:=1)
xNum1 = WorkRng.Row + xInterval
xNum2 = xRows + xInterval
Set xWs = WorkRng.Parent
For i = 1 To Int(xRowsCount / xInterval)
xWs.Range(xWs.Cells(xNum1, WorkRng.Column), xWs.Cells(xNum1 + xRows - 1, WorkRng.Column)).Select
Application.Selection.EntireRow.Insert
xNum1 = xNum1 + xNum2
Next
End Sub
Ive tested it and it seems to work, but you only need select a column to specify which rows you need the macro to act over.
If its not what you want, there are others out there, some of which have the interval fixed, or may auto detect the range to work over. It depends what you want. let me know if you have problems.
Frank
Member
Well, not easily - I dont think as its is specifically designed to work on one sheet only and memorise the range you are working on, on that sheet. Also the delete rows command doesnt need any more information than the row number to work. To selectively copy between 2 sheets needs more tracking of where you are copying from and to as it swaps pages and needs to check whether you are overwriting anything when you paste. In my opinion that is using a sledgehammer to crack a nut when you can easily do it using formulas.
I am assuming that you only want to copy a single row at each increment, and you have chosen cell Q1 on sheet2 to enter the value of N. I also assume that sheet 1 is currently blank over the same number of columns that sheet 2 has values.
On sheet 1 in cell A1 enter the formula:-
=IF(Sheet2!$Q$1,IF(MOD(ROW(),Sheet2!$Q$1)=0,IF(NOT(ISBLANK(Sheet2!A1)),Sheet2!A1,""),""),"")
copy that formula across the row as far as you need, and then copy the whole row of formulas down as many rows as you need.
Fill in your value of N in cell Q1 on sheet 2. You can change the formula to use a different cell from Q1 if you wish (change $Q$1 in2 places).
This will copy the rows of sheet 2 at increments of N onto sheet 1.
This does leave blank rows between the ones you want to see on sheet 1, this is only a problem if N is high. You could use Autofilter on sheet 1 over the used range to filter out the blanks to see them all contiguously, but if you did that you wouldn't see row 1 for the filter arrows. You could cure this by entering the formula not in A1 but in A2 and copying across and down. This leaves row 1 on sheet 1 free for the autifilter arrows.
Is this any good to you ?
Frank
I am assuming that you only want to copy a single row at each increment, and you have chosen cell Q1 on sheet2 to enter the value of N. I also assume that sheet 1 is currently blank over the same number of columns that sheet 2 has values.
On sheet 1 in cell A1 enter the formula:-
=IF(Sheet2!$Q$1,IF(MOD(ROW(),Sheet2!$Q$1)=0,IF(NOT(ISBLANK(Sheet2!A1)),Sheet2!A1,""),""),"")
copy that formula across the row as far as you need, and then copy the whole row of formulas down as many rows as you need.
Fill in your value of N in cell Q1 on sheet 2. You can change the formula to use a different cell from Q1 if you wish (change $Q$1 in2 places).
This will copy the rows of sheet 2 at increments of N onto sheet 1.
This does leave blank rows between the ones you want to see on sheet 1, this is only a problem if N is high. You could use Autofilter on sheet 1 over the used range to filter out the blanks to see them all contiguously, but if you did that you wouldn't see row 1 for the filter arrows. You could cure this by entering the formula not in A1 but in A2 and copying across and down. This leaves row 1 on sheet 1 free for the autifilter arrows.
Is this any good to you ?
Frank
