auto insert blank line

[Little pete]

wanting to auto insert a blank line every 'x' number of lines down the s/s.
for the example 'x' can be every 4th line

cheers pete

 

[Paul B]

Pete, here is some code that will do it,

Sub testme()
'will add a row after every 4th row
'Original code by: Dave Peterson
Dim iCtr As Long
Dim LastRow As Long
Dim myRng As Range
With ActiveSheet
LastRow = .Cells.SpecialCells(xlCellTypeLastCell).Row
Set myRng = Nothing
For iCtr = 5 To LastRow Step 4
If myRng Is Nothing Then
Set myRng = .Cells(iCtr, "A")
Else
Set myRng = Union(.Cells(iCtr, "A"), myRng)
End If
Next iCtr
End With

If myRng Is Nothing Then
'do nothing
Else
myRng.EntireRow.Insert
End If
End Sub



--
Paul B
Always backup your data before trying something new
Please post any response to the newsgroups so others can benefit from it
Feedback on answers is always appreciated!
Using Excel 2002 & 2003

 

[run, I've got the keys]

Paul,
I've attempted to use your script to insert a line every other row, but have
not been successful. I can alter it to insert a line every two rows, but
when I change line "For iCtr = 5 To LastRow Step 4" to "For iCtr = 2 To
LastRow Step 1" my table disappears.

Any suggestions?

Thank you,
Brian

 

[Dave Peterson]

If you scroll down to the bottom of your worksheet, you'll see the table. Since
you used a step of 1, it saw the whole table as a giant range (instead of
individual cells). And inserted as many rows as there are rows in your table.

If you want to insert a row between each row (I wouldn't do this), you could use
this (that works from the bottom up):

Option Explicit
Sub testme2()
Dim iCtr As Long
Dim LastRow As Long
Dim myRng As Range
With ActiveSheet
LastRow = .Cells.SpecialCells(xlCellTypeLastCell).Row
Set myRng = Nothing
For iCtr = LastRow To 2 Step -1
.Rows(iCtr).Insert
Next iCtr
End With
End Sub

The reason I wouldn't do this is that those blank rows really mess up things
like autofilter, charts, pivottables.

If you agree (and come to your senses <vbg>), maybe just doubling the rowheight
would be sufficient.

You could use something like:

Option Explicit
Sub testme2A()
Dim iCtr As Long
Dim LastRow As Long
Dim myRng As Range
With ActiveSheet
.UsedRange.Rows.AutoFit
LastRow = .Cells.SpecialCells(xlCellTypeLastCell).Row
Set myRng = Nothing
For iCtr = LastRow To 2 Step -1
.Rows(iCtr).RowHeight = .Rows(iCtr).RowHeight * 2
Next iCtr
End With
End Sub

 

[run, I''ve got the keys]

Thanks Dave, that worked out great!

If you scroll down to the bottom of your worksheet, you'll see the table. Since
you used a step of 1, it saw the whole table as a giant range (instead of
individual cells). And inserted as many rows as there are rows in your table.

If you want to insert a row between each row (I wouldn't do this), you could use
this (that works from the bottom up):

Option Explicit
Sub testme2()
Dim iCtr As Long
Dim LastRow As Long
Dim myRng As Range
With ActiveSheet
LastRow = .Cells.SpecialCells(xlCellTypeLastCell).Row
Set myRng = Nothing
For iCtr = LastRow To 2 Step -1
.Rows(iCtr).Insert
Next iCtr
End With
End Sub

The reason I wouldn't do this is that those blank rows really mess up things
like autofilter, charts, pivottables.

If you agree (and come to your senses <vbg>), maybe just doubling the rowheight
would be sufficient.

You could use something like:

Option Explicit
Sub testme2A()
Dim iCtr As Long
Dim LastRow As Long
Dim myRng As Range
With ActiveSheet
.UsedRange.Rows.AutoFit
LastRow = .Cells.SpecialCells(xlCellTypeLastCell).Row
Set myRng = Nothing
For iCtr = LastRow To 2 Step -1
.Rows(iCtr).RowHeight = .Rows(iCtr).RowHeight * 2
Next iCtr
End With
End Sub