insert blank row every 10 rows
 

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?

insert blank row every 10 rows
 

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


--


Gary Keramidas
Excel 2003


"aileen" wrote in message
...
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?

insert blank row every 10 rows
 

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

--
Regards,

OssieMac


"aileen" wrote:

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?

insert blank row every 10 rows
 

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

"aileen" wrote:

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?

insert blank row every 10 rows
 

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)


"aileen" wrote in message
...
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?

insert blank row every 10 rows
 

Worked perfectly. Thanks so much.

"Rick Rothstein" wrote:

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)


"aileen" wrote in message
...
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?

.