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.

TIA,
Daisy


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

Daisy,

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

Basically
Sub newrow()
Range("A1").Select
Do Until ActiveCell.Value = ""
ActiveCell.Offset(10, 0).Activate
ActiveCell.Rows.Select
Selection.Insert shift:=xlDown
ActiveCell.Offset(1, 0).Activate
Loop


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


Daisy,

you could go to asap-utilities.com 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.

Daisy

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

THANKS FOR THE FORMULA BUT ??

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,

Daisy