Inserting rows below last row

[perera3112]

Hi. I want to create a macro that creates a new row below the last row in a set of cells, and then to paste into the new row, what is in the row above it. Then select the penultimate row, and paste special, values. can anybody help?

Thanks very much!

[MarvinP]

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:

Please Login or Register  to view this content.

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:

Please Login or Register  to view this content.

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:

Please Login or Register  to view this content.

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()

Please Login or Register  to view this content.

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

Please Login or Register  to view this content.

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"

Please Login or Register  to view this content.

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.

[perera3112]

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!

[MarvinP]

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:

Please Login or Register  to view this content.

hope this helps.