How to automatically insert every other 10 row?

linuxya

New Member
Joined
Mar 29, 2022
Messages
11
Office Version
  1. 2016
Platform
  1. Windows
I would like to insert a single row after 10, 20, 30, etc.
Example,
1
2
3
4
5
6
7
8
9
10
(new row)

row.jpg
 

Excel Facts

Workdays for a market open Mon, Wed, Friday?
Yes! Use "0101011" for the weekend argument in NETWORKDAYS.INTL or WORKDAY.INTL. The 7 digits start on Monday. 1 means it is a weekend.
Try this with a copy of your data. I have assumed column C is empty. If not, choose another column.

VBA Code:
Sub InsertRows()
  With Range("C1:C" & Range("B" & Rows.Count).End(xlUp).Row)
    .Value = Evaluate("row(1:" & .Rows.Count & ")")
    .Replace What:="*?1", Replacement:=True, LookAt:=xlWhole
    .SpecialCells(xlConstants, xlLogical).EntireRow.Insert
    .ClearContents
  End With
End Sub
 
Upvote 0
B1=1
B2=
Code:
=IF(MOD(ROW(),11)=0,"",IF(ROW()=1,1,MAX($B$1:B1)+1))
drag down
 
Upvote 0
Try this with a copy of your data. I have assumed column C is empty. If not, choose another column.

VBA Code:
Sub InsertRows()
  With Range("C1:C" & Range("B" & Rows.Count).End(xlUp).Row)
    .Value = Evaluate("row(1:" & .Rows.Count & ")")
    .Replace What:="*?1", Replacement:=True, LookAt:=xlWhole
    .SpecialCells(xlConstants, xlLogical).EntireRow.Insert
    .ClearContents
  End With
End Sub
Strangely, I have to copy my existing data to two columns to make the code works

B1=1
B2=
Code:
=IF(MOD(ROW(),11)=0,"",IF(ROW()=1,1,MAX($B$1:B1)+1))
drag down
This is great, but I got different data
 
Upvote 0
Strangely, I have to copy my existing data to two columns to make the code works
Works for me with a single column of data.
My sample sheet before the code is run

22 06 07.xlsm
ABC
1Data 1
2Data 2
3Data 3
4Data 4
5Data 5
6Data 6
7Data 7
8Data 8
9Data 9
10Data 10
11Data 11
12Data 12
13Data 13
14Data 14
15Data 15
16Data 16
17Data 17
18Data 18
19Data 19
20Data 20
21Data 21
22Data 22
23Data 23
24Data 24
25Data 25
26Data 26
27Data 27
28Data 28
29Data 29
30Data 30
31Data 31
32Data 32
Insert Rows


After

22 06 07.xlsm
B
1Data 1
2Data 2
3Data 3
4Data 4
5Data 5
6Data 6
7Data 7
8Data 8
9Data 9
10Data 10
11
12Data 11
13Data 12
14Data 13
15Data 14
16Data 15
17Data 16
18Data 17
19Data 18
20Data 19
21Data 20
22
23Data 21
24Data 22
25Data 23
26Data 24
27Data 25
28Data 26
29Data 27
30Data 28
31Data 29
32Data 30
33
34Data 31
35Data 32
36
Insert Rows



.. or perhaps I have not understood the requirement?
 
Upvote 0
.. or perhaps I have not understood the requirement?

Other persons assist me on this, thanks for your time.

VBA Code:
Sub Add_row_after_10()
Dim rwArr, i As Long, ii As Long, j As Long, rng As Range
ReDim rwArr(1 To Application.WorksheetFunction.RoundUp(Cells(Rows.Count, 1).End(xlUp).Row / 10, 0))
Application.ScreenUpdating = False

i = 1

    For j = LBound(rwArr) To UBound(rwArr)
        i = i + 10
        rwArr(j) = i
    Next j

    For ii = LBound(rwArr) To UBound(rwArr)
        If rng Is Nothing Then
            Set rng = Rows(rwArr(ii))
                Else
            Set rng = Union(rng, Rows(rwArr(ii)))
        End If
    Next ii
    
    Intersect(rng.Rows, Columns(1)).EntireRow.Insert

Application.ScreenUpdating = True

End Sub
 
Upvote 0
Other persons assist me on this,
That code requires data in column A. In post #1 you showed data in column B only. ;)

If you change this one value in my code it should do the same thing with no looping required.
Rich (BB code):
Sub InsertRows()
  With Range("C1:C" & Range("A" & Rows.Count).End(xlUp).Row)
    .Value = Evaluate("row(1:" & .Rows.Count & ")")
    .Replace What:="*?1", Replacement:=True, LookAt:=xlWhole
    .SpecialCells(xlConstants, xlLogical).EntireRow.Insert
    .ClearContents
  End With
End Sub

.. and if you actually have more than one column of data, it just requires one changed line
Rich (BB code):
Sub InsertRows_v2()
  With Cells(1, ActiveSheet.UsedRange.Columns.Count + 1).Resize(Range("A" & Rows.Count).End(xlUp).Row)
    .Value = Evaluate("row(1:" & .Rows.Count & ")")
    .Replace What:="*?1", Replacement:=True, LookAt:=xlWhole
    .SpecialCells(xlConstants, xlLogical).EntireRow.Insert
    .ClearContents
  End With
End Sub
 
Upvote 0
Other persons assist me on this, thanks for your time.
For future reference, this is known as Cross-posting (posting the same question in more than one forum) is not against our rules, but the method of doing so is covered by #13 of the Forum Rules.

Be sure to follow & read the link at the end of the rule too!

Cross posted at: How to automatically insert every other 10 row VBA?
If you have posted the question at more places, please provide links to those as well.

If you do cross-post in the future and also provide links, then there shouldn’t be a problem.
 
Upvote 0

Forum statistics

Threads
1,213,543
Messages
6,114,236
Members
448,555
Latest member
RobertJones1986

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top