Thread: Inserting rows below last row

Hi perera3112,

Your request sounds a lot like "Teach me VBA" - so here we go.
I opened a new workbook and put stuff in A1, A2 and A3. Then I turned on the "Record Macro" and clicked below the last row in Column A. Then did a Ctrl+Up, moved down a Row and Inserted a row and then copied the row above it to the inserted row. I think this is what you wanted in words. I then turned off the Macro Recorder and looked at the Macro. It showed:

Sub Macro1()
'
' Using Macro Recorder
    Range("A31").Select
    Selection.End(xlUp).Select
    Rows("4:4").Select
    Selection.Insert Shift:=xlDown
    Rows("3:3").Select
    Selection.Copy
    Range("A4").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
End Sub

The recorded macro gave me the vocabulary to do what you want in general. The first problem that it didn't do was tell me the last row of data, as we know it will change.

I've memorized the following line of code:

LastRow = Cells(Rows.Count, "A").End(xlUp).Row

The above will go to the bottom of any sheet in column A and do the Ctrl+Up and stop when it hits the first non-blank cell. That is the Last Row of data on my sheet.

Using the above I Edit my macro to the following:

Sub Macro1()
'
' Using Macro Recorder then editing my macro

Dim LastRow As Double
LastRow = Cells(Rows.Count, "A").End(xlUp).Row
Rows(LastRow + 1 & ":" & LastRow + 1).Insert shift:=xlDown
Rows(LastRow & ":" & LastRow).Copy
Cells(LastRow + 1,"A").Select

Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
End Sub

I don't believe inserting a row below the last row will do anything that is needed so I'll throw that line out and this would leave:
Sub Macro1()

Dim LastRow As Double

    LastRow = Cells(Rows.Count, "A").End(xlUp).Row
    Rows(LastRow & ":" & LastRow).Copy
    Cells(LastRow + 1, "A").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
            :=False, Transpose:=False
End Sub

So the above code is a learning experience from recording a macro to putting in some variables (LastRow) and editing the recorded macro so it works like we want. You should learn the LastRow line above and also learn Cells(row,column) notation.

Note - I had to trial and error a few times as

Rows(LastRow : LastRow).Copy

wasn't understood by the Compiler. I decided to put in the ":" with the & and it finally worked.
I also forgot to put the "A" in the line of code of"

Cells(LastRow + 1, "A").Select

so it didn't work either the first time through. All programmers need to trial and error (or debug) their code before it works.

I hope the above (long answer) will show you how to write your own VBA in the future.

Thanks very much for the above. However, the problem with the above is that it will end(x1up) from the bottom of the sheet. I have cells underneath the cells Im dealing with - I have included an attachment with what im talking about.

Thanks again!

Hi perera,

If you have data like your example you can find the bottom of a list by starting at the top and doing what is in keystroks a Ctrl+Down. The problem is that there is always some blank cell before the real bottom and you get an answer that you didn't want. To find the bottom of the first list in your example you would use the code:

LastRow = Cells(5,"B").End(xlDown).Row

hope this helps.