insert blank row every 10 rows

[aileen]

Is it possible to insert a blank row every 10th row starting from row 11 and
going until the last and variable row of data?

 

[Gary Keramidas]

maybe this?

Sub test()
Dim ws As Worksheet
Dim lastrow As Long
Set ws = Worksheets("Sheet1")
lastrow = ws.Cells(Rows.Count, "A").End(xlUp).Row
Do Until lastrow < 11
If lastrow Mod 10 = 0 Then
ws.Rows(lastrow).Offset(-9).EntireRow.Insert
End If
lastrow = lastrow - 1
Loop
End Sub

 

[OssieMac]

Hi Aileen,

I am assuming that you mean that you want 10 rows with data then a blank row
then 10 rows with data etc. If not getting the result you want then see the
comments and edit the code.

Suggest that you back up your workbook before running the code.

Sub InsertRows()

Dim lngInsert As Long
Dim lngIntervals As Long

'Edit 11 in next line with number
'of row to start inserting.
lngInsert = 11

'Edit 10 in next line with number of
'rows with data between inserted rows
lngIntervals = 10

lngIntervals = lngIntervals + 1

ActiveSheet.UsedRange.Cells _
.SpecialCells(xlCellTypeLastCell) _
.EntireRow.Name = "MyLastRow"

Do
ActiveSheet.Rows(lngInsert).Insert
lngInsert = lngInsert + lngIntervals
Loop While lngInsert < Range("MyLastRow").Row

End Sub

 

[Mike H]

Hi,

try this

Sub insertrow()
Dim LastRow As Long, x As Long
LastRow = Cells(Cells.Rows.Count, "A").End(xlUp).Row
LastRow = LastRow - (LastRow Mod 10) + 1
For x = LastRow To 11 Step -10
Rows(x).Insert
Next x
End Sub

Mike

 

[Rick Rothstein]

Your question is not entirely clear as to which row should be the first
blank row... 11 or 12. Assuming 11, try this macro...

Sub InsertRowsEveryTenthRow()
Dim X As Long, FirstBlankRow As Long, Increment As Long, U As Range
FirstBlankRow = 11
Increment = 10
For X = FirstBlankRow To ActiveSheet.UsedRange.Rows.Count Step Increment
If U Is Nothing Then
Set U = Rows(X)
Else
Set U = Union(U, Rows(X))
End If
Next
U.Insert
End Sub

 

[aileen]

Worked perfectly. Thanks so much.

Your question is not entirely clear as to which row should be the first
blank row... 11 or 12. Assuming 11, try this macro...

Sub InsertRowsEveryTenthRow()
Dim X As Long, FirstBlankRow As Long, Increment As Long, U As Range
FirstBlankRow = 11
Increment = 10
For X = FirstBlankRow To ActiveSheet.UsedRange.Rows.Count Step Increment
If U Is Nothing Then
Set U = Rows(X)
Else
Set U = Union(U, Rows(X))
End If
Next
U.Insert
End Sub

--
Rick (MVP - Excel)




.