How to Insert A New Between After a Specific Row

Question

Can you please let me know how I can
1- insert a new row let say 3 exactly after Existing row 2
2- Copy Strings from 1 to 2
3- and finally delete 1

The reason that i have to do this is I have some Drop-down, look ups(which I do not know what they call in excel) in row 1 and i can not load the file like this into GIS software so I need to get rid of that, however I still need to keep the Title headers for further referencing.

thanks Update

Sub inserter()
'
' inserter Macro
'

'
    Rows("3:3").Select
    Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
    Rows("2:2").Select
    Selection.Copy
    Rows("3:3").Select
    ActiveSheet.Paste
    Rows("2:2").Select
    Application.CutCopyMode = False
    Selection.Delete Shift:=xlUp
End Sub

Record a macro and see how it happens and then simply amend the macro? — Siddharth Rout, Dec 17 '13 at 04:45
Thanks Siddharth, can you please check the updated post if I am doing correct? — Behseini, Dec 17 '13 at 04:50

score 1 · Answer 1 · answered Dec 17 '13 at 04:51

Check this code:

Rows("3:3").Select
Selection.Delete Shift:=xlUp
Range("B4").Select
ActiveCell.FormulaR1C1 = "hello"
Range("B4").Select
Selection.Copy
Range("C4").Select
ActiveSheet.Paste
Rows("4:4").Select
Application.CutCopyMode = False
Selection.Delete Shift:=xlUp

but better learn to save macros. I mean I dont have in my head this code, everytime I need some code I just save it, btw avoid .select, do something like this instead:

Rows("3:3").Delete Shift:=xlUp

Hope it helps

score 1 · Accepted Answer · edited May 23 '17 at 12:12

Um not exactly. If you notice this line

.Rows(1).Copy .Rows(2)

It follows what you had in point 2 and it not as per what you recorded.

Macro recorder gives all kinds of extra code. The code that you gave can be written as

Private Sub Sample()
    Dim ws As Worksheet

    Set ws = ThisWorkbook.Sheets("Sheet1")

    With ws
        .Rows(3).Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
        .Rows(1).Copy .Rows(2)
        .Rows(1).Delete Shift:=xlUp
    End With
End Sub

You might also want to see THIS

How to Insert A New Between After a Specific Row

2 Answers2