Insert number new rows based on preceeding row. Populate them based on preceeding row.

[Common Wealth]

Good Afternoon,


I am looking for a macro solution to insert and populate a new number of rows equal to the difference between two numbers in the row preceding it.

The rows of data that I already have two columns that represent varying real life numerical ranges. Example Row F=1 Row G=115 represent the real life range 1 through 115. I would like to macro to insert 115 new rows, and then populate those new rows with the numbers in that range, in thie case (1,2,3,4....115) I need it to work for 1 to 10,000 rows.

I would like to be able to toggle between showing and hiding only the newly inserted rows. So that when the newly inserted rows are hidden the data appears like the original sheet before running the insert/populate macro.


I have attached an abbreviated mock workbook. Sheet 1 is the original sheet. Sheet 2 is the desired sheet. Original data is in blue, 'imaginary' data for the desired example is shown in orange. Mockup2.xls


**Also I posted this a few days ago another user replied with a code that only worked partially. I didn't work for me - not discrediting his work I just couldn't get it to work. I have also clarified my post further this time, so perhaps I wasn't clear with the previous helper. But here is a link to the first post for what it may provide: http://www.excelforum.com/excel-prog...10#post3078110


Thanks very much,

Becky

[mrice]

Please try this

Please Login or Register  to view this content.

Open up the VBA editor by hitting ALT F11

Insert a new module by hitting Insert - Module

Paste the macro into the empty sheet

Hit ALT F11 to get back to the worksheet.

Run the macro by going to tools-macro in Excel 2003 or the view ribbon in Excel 2007/2010.

[Common Wealth]

Hi Martin,

Thanks for the reply. I tried it out, and got the following error from Microsoft Visual Baisc: "Run-time error '9': Subscript out of range"

I am using Excel 2011 on a Mac. (unfortunately :p )

[mrice]

Are you using this on the example workbook or another one? This error often indicates that the sheets have different names.

[Common Wealth]

I am trying to use it in a different workbook, I created the sample workbook on a pc using open office, but need to run it on a mac with excel 2011. Is there a way to allow for this?

Thank you!!

[Common Wealth]

I see - I just needed to rename the sheets/and or the code. It has worked for me now. Thank you Very Much!! I really appreciate it!