Add Rows between items

[RSa]

Running Excel 2010. Need help creating a macro this will add (for now) 10 blank rows between each change in item #. Item #'s will be in column B of spreadsheet.
I've tried several different macros and can't seem to get any of them to work correctly. Eventually I will be adding like 184 rows between each item #, but for now let's just concentrate on 10 blank rows.

Appreciate any help.

attached is a clip of what I'm working with . There is a header Row in Row 1 and data starts in Row 2 with the Item #'s in column B.

[JieJenn]

Is this what you are looking for?

Please Login or Register  to view this content.

[RSa]

Add Rows.xls

please see new attached. in your previous macro it added one blank row between each item #.
attached is new spreadsheet with rows 2 & 3 actually having the same item #.
Macro needs to insert the blank rows between each change in value & add 10 blank rows instead of 1.

Thanks

[jindon]

See if this is what you want

Please Login or Register  to view this content.

[RSa]

that added 10 blank rows between all #'s again. didn't recognize that rows 2 & 3 were same item # & don't need blank rows between them, same with rows 6 & 7

[jindon]

Then

Please Login or Register  to view this content.

[jomili]

Withdrawn. Didn't cover the 10 or the 184.

[RSa]

ok - that worked great. but lets say that rows 1,2,3, & 4 all have the same item #, so the formula can't actually just start in row 3. this could vary each time, could be the same item # in just 1 row or it could be the same for 100 + rows before changing to new a new #.

[jindon]

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.

[jomili]

RSA,
try this one.

Please Login or Register  to view this content.

[jindon]

Don't understand what you are saying...

[RSa]

jomili- this worked great to an extent. It added my desired # of rows between each change it item #. It did however add blank rows just underneath the header row which is in row 1. Don't need blank rows between heard and first item #. Item #'s will always be in column B so can we get rid of the message box asking for which row we want to analyze?

[jomili]

We could get rid of the message box, but that would force you to preselect the range of rows to add the extra rows in. Otherwise, without a defined area, the macro will work on the entire sheet, so every row, which will slow it down considerably.

If you don't want rows added after header, select from Row2 down to the last row for which you want to insert rows.

[RSa]

I tried selecting from row 2 down to last row & it still added rows after the header

[jomili]

Sorry, you're right. The selection thing is intended to determine the last row you want to do blank rows in, not the first. Changed it in the code below (see line in red). Also hardcoded Column B (you'll have to change this if your structure ever changes)

Please Login or Register  to view this content.

[RSa]

It worked - AWESOME - Thanks