Help on basic macro code - inserting row

[Dohoch]

I just started using Macros/VBA, so I am hoping someone could help me get over this first hurdle. Thanks a lot.

The below formula copies row 33 and inserts it into row 36. Instead of row 36, i would like it to show up on the first empty row. For example, if there is information in rows 1 - 40, i would like row 33 to be inserted into row 41. And then the next time i run the macro, it will be inserted into row 42, and so on.

Rows("33:33").Select
Selection.Copy
Rows("36:36").Select
Selection.Insert Shift:=xlDown


​

 

[Tom Urtis]

You can do that with this one-liner:

Rows(33).Copy Rows(Cells.Find(What:="*", After:=Range("A1"), SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row + 1)

 

[Rick Rothstein]

You can do that with this one-liner:

Rows(33).Copy Rows(Cells.Find(What:="*", After:=Range("A1"), SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row + 1)

You can shorten that line of code a little bit (the After argument is not needed as A1 is the default cell when not specified)...

Rows(33).Copy Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Offset(1).EntireRow(1)

 

[Tom Urtis]

Shorter code does not necessarily mean better code. Your way trades off an already built-in argument for an additional unnecessary method.

 

[Rick Rothstein]

Shorter code does not necessarily mean better code. Your way trades off an already built-in argument for an additional unnecessary method.

Apparently I screwed up the copy/paste operation as the code I ended up posting was not the code I meant to post (that was an experiment that didn't work out) as it is longer than the code that resulted from simply modifying your original code...

Rows(33).Copy Rows(Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row + 1)

 

[Dohoch]

Thanks for the replies.

I tried the code, but it keeps erasing the information I put in using the Macro. For example, after I run the macro which puts data in the row, if i run the Macro again it will replace that row with new information.

I am looking to run the macro and have information put into the first available row in the table. Then when I run the macro again, it would insert the new information under that row. I put an illustration below.

Row 1: info
Row 2: info
Row 3: info
Row 4: info
Row 5: blank (run Macro to put info in row 5)

after macro was used

Row 1: info
Row 2: info
Row 3: info
Row 4: info
Row 5: info
Row 6: Blank (run Macro to put info in row 6)

and so on

Maybe I am putting the code in wrong. Thanks again.