Consulting

Results 1 to 3 of 3

Thread: Insert New Rows Between Rows

Threaded View

Previous Post Previous Post   Next Post Next Post
  1. #1

    Insert New Rows Between Rows

    Hey guys,
    I found out that selecting multiple rows by hand and then inserting rows would result in inserting new rows between the selected rows.
    The following macro shows this:

    Sub Macro1()
        Range("1:1,2:2,3:3").Select
        Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
    End Sub
    Now, what I need to do is this:

    Sub test()
        'Getting the last Row
        Cells(1, 1).Select
        lastRow = Selection.End(xlDown).Row
        'Building the Selection
        For i = 1 To lastRow
            selectionString = selectionString & i & ":" & i & ","
        Next i
        'Removing the last ","
        selectionString = Mid(selectionString, 1, Len(selectionString) - 1)
        Range(selectionString).Select
        Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
    End Sub
    This is trying to select each row individually to insert rows between the selected rows. As expected, I get the following error as it tries to select the rows:

    Run-time error '1004':

    Method 'Range' of object 'Global_' failed

    I know there are plenty of ways to achieve the same result by creating a simple for-loop and inserting a new row after each row.
    This is NOT what I want to do, since this seems to be a lot slower. (I am speaking about 200.000-300.000 rows)
    Any ideas no how to get this working without a loop?
    Last edited by Aussiebear; 12-18-2024 at 04:14 AM.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •