Inserting Lines
 

IS there a way to program Excel to insert a new line. For
example I need to have a new line instered every 23 lines
in a spreadsheet I am working on. Your help would be
greatly appreciated!

Christie

Inserting Lines
 

Try this

Sub test()
Application.ScreenUpdating = False
Dim numRows As Integer
Dim R As Long
Dim Rng As Range
numRows = 1
Set Rng = ActiveSheet.UsedRange
For R = Rng.Rows.Count To 1 Step -23
Rng.Rows(R + 1).Resize(numRows).EntireRow.insert
Next R
Application.ScreenUpdating = True
End Sub


--
Regards Ron de Bruin
(Win XP Pro SP-1 XL2002 SP-2)
www.rondebruin.nl



"C" wrote in message ...
IS there a way to program Excel to insert a new line. For
example I need to have a new line instered every 23 lines
in a spreadsheet I am working on. Your help would be
greatly appreciated!

Christie

Inserting Lines
 

This will only work if your rows count is a multiple from 23
you can check that in the code also and make the range bigger ?

--
Regards Ron de Bruin
(Win XP Pro SP-1 XL2002 SP-2)
www.rondebruin.nl



"Ron de Bruin" wrote in message ...
Try this

Sub test()
Application.ScreenUpdating = False
Dim numRows As Integer
Dim R As Long
Dim Rng As Range
numRows = 1
Set Rng = ActiveSheet.UsedRange
For R = Rng.Rows.Count To 1 Step -23
Rng.Rows(R + 1).Resize(numRows).EntireRow.insert
Next R
Application.ScreenUpdating = True
End Sub


--
Regards Ron de Bruin
(Win XP Pro SP-1 XL2002 SP-2)
www.rondebruin.nl



"C" wrote in message ...
IS there a way to program Excel to insert a new line. For
example I need to have a new line instered every 23 lines
in a spreadsheet I am working on. Your help would be
greatly appreciated!

Christie

Inserting Lines
 

Christie,

Enter the following macro in a code module (Alt+F11, Insert-Module):

'------
Option Explicit

Sub insertLines()
Dim startRow As Long, currRow As Long
Dim rowSpacing As Integer

startRow = 24 ' Enter first row to insert
rowSpacing = 23 ' Enter # of rows in each block

currRow = startRow

Do While True
If Application.Intersect(Rows(currRow), ActiveSheet.UsedRange) _
Is Nothing = True Then Exit Sub

ActiveSheet.Rows(currRow).Insert
currRow = currRow + rowSpacing + 1
Loop

End Sub
'------

HTH
Anders Silvén

"C" skrev i meddelandet
...
IS there a way to program Excel to insert a new line. For
example I need to have a new line instered every 23 lines
in a spreadsheet I am working on. Your help would be
greatly appreciated!

Christie