Inserting new row after every nth row - problem

EdwardL95

New Member
Joined
Aug 17, 2021
Messages
10
Office Version
  1. 365
Platform
  1. Windows
Hi all new to VBA,

Task: Insert a new row every 12th row starting at cell A14 with the text "NEG".

Problem: After running the macro it will miss the last NEG in the last set of data. Possibly due to the last row changing after the macro adds in new rows and pushing the data down?

Is there a way to work around this?

Code below I've been using found on this website.

Option Explicit

Sub Evry12()
Dim i As Long
Dim lr As Long
lr = Range("A" & Rows.Count).End(xlUp).Row
For i = 14 To lr
Range("A" & i).EntireRow.Insert
Range("A" & i) = "NEG"
i = i + 11
Next i
End Sub

Thanks,
Ed
 

Excel Facts

Select a hidden cell
Somehide hide payroll data in column G? Press F5. Type G1. Enter. Look in formula bar while you arrow down through G.

Marc L

Well-known Member
Joined
Apr 5, 2021
Messages
1,758
Office Version
  1. 2010
Platform
  1. Windows
Is there a way to work around this?
Hi, just following the Logic :​
VBA Code:
Sub Demo1()
    Dim R&
        R = 14
        Application.ScreenUpdating = False
    While Not IsEmpty(Cells(R, 1))
        Rows(R).Insert
        Cells(R, 1).Value2 = "NEG"
        R = R + 11
    Wend
        Application.ScreenUpdating = True
End Sub
 
Solution

EdwardL95

New Member
Joined
Aug 17, 2021
Messages
10
Office Version
  1. 365
Platform
  1. Windows
Hi, just following the Logic :​
VBA Code:
Sub Demo1()
    Dim R&
        R = 14
        Application.ScreenUpdating = False
    While Not IsEmpty(Cells(R, 1))
        Rows(R).Insert
        Cells(R, 1).Value2 = "NEG"
        R = R + 11
    Wend
        Application.ScreenUpdating = True
End Sub

Thanks Marc.
 

Marc L

Well-known Member
Joined
Apr 5, 2021
Messages
1,758
Office Version
  1. 2010
Platform
  1. Windows
My previous demonstration revamped for non smart worksheet as well :​
VBA Code:
Sub Demo1r()
    Dim R&, Rg As Range
        R = 14
        Set Rg = Cells(Rows.Count, 1).End(xlUp)
        Application.ScreenUpdating = False
    While R <= Rg.Row
        Rows(R).Insert
        Cells(R, 1).Value2 = "NEG"
        R = R + 11
    Wend
        Application.ScreenUpdating = True
        Set Rg = Nothing
End Sub
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
52,251
Office Version
  1. 365
Platform
  1. Windows
Welcome to the MrExcel board!

Another option would be to do them all at once.

VBA Code:
Sub Insert_Every_10_Rows()
  Application.ScreenUpdating = False
  With Cells(14, Cells.Find("*", , xlFormulas, , , xlByColumns, xlPrevious).Column + 1).Resize(Range("A" & Rows.Count).End(xlUp).Row - 13)
    .Value = Evaluate("if(right(row(" & .Address & "),1)=""" & Right(.Cells(1).Address, 1) & """,1,"""")")
    .SpecialCells(2, 1).EntireRow.Insert
    Intersect(.SpecialCells(2, 1).EntireRow, Columns("A")).Offset(-1).Value = "NEG"
    .ClearContents
  End With
  Application.ScreenUpdating = True
End Sub
 
Learn Excel from Bill Jelen

Understanding data is crucial, and the easiest place to start is with Microsoft Excel.

Forum statistics

Threads
1,151,608
Messages
5,765,403
Members
425,284
Latest member
fishymuffin

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
Top