Sub InsertRowEveryXrows()
Dim rw As Long
Dim lr As Long
Dim cnt As Long
lr = Range("A" & Rows.Count).End(xlUp).Row
rw = 1
cnt = 1
Do
If cnt = 10 Then
Rows(rw).Insert Shift:=xlDown
cnt = 1
Else
cnt = cnt + 1
End If
rw = rw + 1
Loop While rw <> lr
End Sub
If cnt = 10 Then
Sub Insert_Row()
Dim my As Integer, ur As Integer
On Error GoTo Getout
ur = InputBox("Enter How Many Row Want to Insert")
my = InputBox("Enter How Many Rows Want to Skip")
Application.ScreenUpdating = False
If my = 0 Or ur = 0 Then Exit Sub
On Error GoTo Getout
Range("A" & 2 + my).Select
Do While ActiveCell.Value <> ""
5 Range(ActiveCell, ActiveCell.Offset(ur - 1, 0)).EntireRow.Insert
ActiveCell.Offset(1 + my + ur - 1, 0).Select
Loop
Getout:
Application.ScreenUpdating = True
End Sub
Sub InsertRowEveryXrows()
Dim rw As Long
Dim lr As Long
Dim cnt As Long
lr = Range("A" & Rows.Count).End(xlUp).Row
rw = 1
cnt = 1
Do
If cnt = 10 Then
Rows(rw).Insert Shift:=xlDown
cnt = 1
lr = Range("A" & Rows.Count).End(xlUp).Row
Else
cnt = cnt + 1
End If
rw = rw + 1
Loop While rw <> lr
End Sub
Sorry Kev, that doesn't work. I entered 1 row every 10 rows in your code and the first row it inserted was row 12, not row 10. The next row was at row 23 then 34, gets off my more and more. Should be 10, 20, 30, 40TRY BELOW CODE ITS ALLOW TO INSERT ROW AS PER YOUR INPUT PROVIDE YOU
Option Explicit
Sub Macro1()
'Written by Trebor76
'Visit my website www.excelguru.net.au
Dim lngMyRow As Long
Dim lngLastRow As Long
Dim rngMyRange As Range
lngLastRow = Cells(Rows.Count, "A").End(xlUp).Row
For lngMyRow = 10 To lngLastRow Step 10
If rngMyRange Is Nothing Then
Set rngMyRange = Cells(lngMyRow, "A")
Else
Set rngMyRange = Union(rngMyRange, Cells(lngMyRow, "A"))
End If
Next lngMyRow
rngMyRange.EntireRow.Insert
Application.ScreenUpdating = True
MsgBox "Rows have now been inserted"
End Sub
sorrySorry Kev, that doesn't work. I entered 1 row every 10 rows in your code and the first row it inserted was row 12, not row 10. The next row was at row 23 then 34, gets off my more and more. Should be 10, 20, 30, 40
Wasn't my post, just pointing out the flaws in the other codes. The do loop I wrote works. Yours has a similar effect as kev's, where every 10 rows should be inserted at row 10, 20, 30, 40. etc.... yours did 10, 21, 32, 43.Hi rjwebgraphix
Try this:
Ah yes, sorry about that.Wasn't my post, just pointing out the flaws in the other codes.
He's name Robert then? Must be a cool guybtw, I get a kick out of it whenever I see your posts. My Dad uses a variation of Trebor on dbforums and when I saw you post the first time I thought you were my dad. LOL
Option Explicit
Sub Macro1()
'Written by Trebor76
'Visit my website www.excelguru.net.au
Dim lngMyRow As Long
Dim lngLastRow As Long
Dim rngMyRange As Range
lngLastRow = Cells(Rows.Count, "A").End(xlUp).Row
For lngMyRow = 10 To lngLastRow Step 10
If lngMyRow <> 10 Then lngMyRow = lngMyRow - 1
If rngMyRange Is Nothing Then
Set rngMyRange = Cells(lngMyRow, "A")
Else
Set rngMyRange = Union(rngMyRange, Cells(lngMyRow, "A"))
End If
Next lngMyRow
rngMyRange.EntireRow.Insert
Application.ScreenUpdating = True
MsgBox "Rows have now been inserted"
End Sub