insert blank row every 10 rows

A

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?
 
G

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
 
O

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
 
M

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
 
R

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
 
A

aileen

Worked perfectly. Thanks so much.

Rick Rothstein said:
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)




.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top