MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Help-How do I Insert a row

Posted by daisy on October 02, 2001 9:22 AM

Hello everyone,

I trying to do the following, I don't even know if it's possible, but hopefully it is because otherwise I have alot of manual cutting and pasting to do ;-(

1. I have a ss with about 100k items, I want to split this sheet up so that I can copy and paste to a new sheet every 10K items

2. I want to insert a blank row every 10k or better yet, insert a row with an item(sameone) every 10k.


Posted by Ryan on October 02, 2001 9:56 AM


Don't know if there is an easier way, but you could create a loop.

Sub newrow()
Do Until ActiveCell.Value = ""
ActiveCell.Offset(10, 0).Activate
Selection.Insert shift:=xlDown
ActiveCell.Offset(1, 0).Activate

End Sub

I am assuming that you have no blank lines already. If that is not the case you could put EOF in the last cell and then change do Until activecell.text = "EOF".

Copying the last row of info is very easy too, I am just not sure exactly what you are trying to do. Do you want to just duplicate row 11 as row 10?

Posted by Michelle on October 02, 2001 10:30 AM


you could go to and download thier addin. it has a function to do this for your. it is great!

Posted by Ryan on October 02, 2001 12:02 PM

Wow this tool is great!! Now I can delete half of the macro's I have written.

Posted by daisy on October 05, 2001 4:26 AM

Thank you all so much for your help, I will try both suggestions.


Posted by daisy on October 05, 2001 5:29 AM


It works great, it does exactly what I wanted. I
can't thank you enough. One question though, and this may sound silly but how do I save this macro to make it available after I close the current sheet and open a new one?

Thans alot,