New Excel Forum

This forum has been moved to TeachExcel.com

Ask all future questions in the New Excel Forum.

ExcelKey

Insert Multiple Rows Without Selecting Anything

Free Excel Macros

Insert Multiple Rows Without Selecting Anything

Postby Sisyphus » Tue Mar 20, 2012 4:20 am

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:
  1. InsRows:= The number of rows you wish to insert
  2. AtRow:= The number of the row to be pushed down.
    This will be the number of the first of the new rows.
  3. 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! :D

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)
Sisyphus
Former Moderator
 
Posts: 4454
Joined: Dec 7, 2011
Location: Shanghai
Reputation: 203
Excel Version: 2010

Return to Excel Macros

Who is online

Users browsing this forum: No registered users and 15 guests