Inserting Lines

  • Thread starter Thread starter C
  • Start date Start date
C

C

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
(e-mail address removed)
 
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
 
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 ?
 
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
 
Back
Top