Macro that inserts rows

  • Thread starter Thread starter Emece
  • Start date Start date
E

Emece

I want to design a macro so as to do the following thing:
Insert a row.
Copy the value that is in a cell of the immediate before
row.
Copy this value in the row inserted.

I want the macro to repeat this in the whole workbook.

I know it is a simple one, but I can't figure it out
correctly.

Thanks a lot
 
Hope the following does what you mean.
Dave Braden

Option Explicit

Sub Macro1()
Dim l As Long, lLast As Long, lFirst As Long, lCt As Long
'determine bounding rows
With ActiveSheet.UsedRange
lFirst = .Rows(1).Row
lCt = .Rows.Count
lLast = .Rows(1).Row + lCt - 1
End With
If lLast - lFirst + 1 > Rows.Count - lLast Then
MsgBox "Not enough room to insert the " & lCt & " rows."
Exit Sub
End If
Application.ScreenUpdating = False
For l = lLast To lFirst Step -1
Rows(l + 1).Insert
Rows(l).Copy Rows(l + 1)
Next
MsgBox lCt & " rows inserted."
End Sub
 
Back
Top