After Every Tenth Row Insert 2 Blank Rows without any Format

[ameenuksg]

Hi I have recorded the following Macro but it seems to insert another extra row(third row) with format which I did not intend to record:

Sub Macro5()
'
' Macro5 Macro
'

'
ActiveCell.Range("A1:A10").Select
ActiveWindow.SmallScroll Down:=3
ActiveCell.Offset(10, 0).Rows("1:1").EntireRow.Select
Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
Selection.Insert Shift:=xlDown
Selection.ClearFormats
ActiveCell.Offset(1, 0).Rows("1:1").EntireRow.Select
Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
ActiveWindow.SmallScroll Down:=3
ActiveCell.Offset(1, 0).Range("A1").Select
End Sub


Anyone able to help me correct the above macro to insert 2 blank rows only after the 10th row without any format and then select the next row after the 2 blank rows ?

 

[Fluff]

Do you have more than 15 rows of data?

 

[ameenuksg]

Nope...last couple of rows were only 11 rows of data and it still inserted blank rows after the 10th row

 

[Fluff]

You said you wanted it not to insert blanks if there were only 15 rows to begin with.
Which is what it does.

 

[ameenuksg]

Sorry I just edited my earlier post..

You said you wanted it not to insert blanks if there were only 15 rows to begin with.
Which is what it does.

 

[Fluff]

Is this what you want

Sub Insertrws()
   Dim lr As Long, i As Long, j As Long
   lr = Range("A" & Rows.Count).End(xlUp).row
   j = ((Int(lr / 10) * 10) + 1)
   For i = IIf(lr - j < 15, j - 10, j) To 11 Step -10
      Rows(i + 1).Resize(2).Insert
      Rows(i + 1).Resize(2).Clear
   Next i
End Sub

 

[ameenuksg]

What a beauty! this is exactly what I wanted..thank you so much!

Is this what you want

Sub Insertrws()
   Dim lr As Long, i As Long, j As Long
   lr = Range("A" & Rows.Count).End(xlUp).row
   j = ((Int(lr / 10) * 10) + 1)
   For i = IIf(lr - j < 15, j - 10, j) To 11 Step -10
      Rows(i + 1).Resize(2).Insert
      Rows(i + 1).Resize(2).Clear
   Next i
End Sub

 

[Fluff]

You're welcome