One would have thought that inserting rows should come easy to VBA, but it doesn't. The "easiest" way seems to be to use key strokes: Select the rows you wish to insert and insert them. Simple syntax like
Rows(15).Insert 5
doesn't meet with VBA's approval. Even the code that, finally, makes do without the Selection object still bears its flavour. But, at least, you don't have to activate the sheet or change your current selection:
- Code: Select all
Public Sub InsertRows(ByVal InsRows As Long, _
ByVal AtRow As Long, _
Optional ByRef Ws As Worksheet)
If Ws Is Nothing Then Set Ws = ActiveSheet
With Ws
.Range(.Rows(AtRow), .Rows(AtRow + InsRows - 1)) _
.Insert xlShiftDown, xlFormatFromLeftOrAbove
End With
End Sub
This procedure requires two arguments and accepts an opetional third:
- InsRows:= The number of rows you wish to insert
- AtRow:= The number of the row to be pushed down.
This will be the number of the first of the new rows. - Optional Ws:= A Worksheet object.
If omitted, the ActiveSheet will receive the rows to be inserted.
NOTE: 'xlFormatFromLeftOrAbove' is the CopyOrigin, meaning the source from where formats will be copied to the new rows. The alternative is 'xlFormatFromRightOrBelow'
Have a great day!
Sisyphus
I do this for "honour and country" - much less of the latter, actually.
If I helped you, award points, plenty of them.
If I bored you, deduct points for being too long-winded. (I know, :lol)