Insert Rows based on Cell Values

First post!

This looks like the best of the excel forums, so here I am..

I've been teaching myself Excel using scroogle, but thought it might be wise to request some help with these macros..

Here's what I'm trying to do:

Please Login or Register  to view this content.

If someone could help me convert this to a macro, it would be much appreciated!

Welcome to the Forum. To best describe or illustrate your problem you would be better off attaching a dummy workbook, the workbook should contain the same structure and some dummy data of the same type as the type you have in your real workbook - so, if a cell contains numbers & letters in this format abc-123 then that should be reflected in the dummy workbook.

If needed supply a before and after sheet in the workbook so the person helping you can see what you are trying to achieve.

Doing this will ensure you get the result you need!

Thank you for the quick reply!

I have created a sample workbook and attached it to the original post as suggested.

Can you tell me why you want to add rows?

I want to add rows to type in new artists/titles to a music collection database.
This macro will help me add the titles on the rows I want them located.

You don't need to add rows. Simply add the da to the bottom of the table, then when finished sort by Artist

I appreciate that suggestion, but I don't want them listed alphabetically.
I want to decide for myself what the location of each title in the sheet should be.

This sheet:"LIST" dictates the order the titles are displayed in for all other sheets in my actual workbook.
It is basically a list of albums which I may or may not have.
Any new titles would first be added to this sheet.
Then, whenever I obtain an album, I copy the row containing that title to another sheet (where I add other info such as the physical condition of the album.

Those other sheets are not relevant to this macro, but hopefully this helps explain why I need the macro to work this way..

Thanks again!

Attached new sample workbook with "AFTER" sheet to show desired results:
In the below attached workbook, the requested macro should:

Skip Rows 1,2,3,4, because $D = "Status","e","g","e".
In Row 5, $D = "t", and $C = "3", so 3 rows should be inserted above Row 5.
In Row 5, change $D = "e" and $C = "".
Skip Row 6, because $D = "e".
In Row 7, $D = "u", and $C = "2", so 2 rows should be inserted below Row 7.
In Row 7, change $D = "e" and $C = "".
Skip Rows 8,9,10, because $D = "g","s","e".
Exit Macro at Row 11, because $D = "*"

Copy down ALL formulas & validation to added rows from above.
For all inserted rows, $C = "", and $D = "s".

Is this what you mean?

Note that I have used the Table feature to make the data into a Table, then Excel automatically copies the formulas

Most likely I just don't know what I'm doing, but that workbook looks to be exactly the same as the one I uploaded..
no table.
no formula.
If I'm wrong (very likely) please tell me how to find/run the macro..

P.S.
Please see new workbook attached above your most recent post for desired results.

Sorry I've attached the wrong workbook

Or from the Insert Tab

It works at least partially.
The original I uploaded had an error. The value "2" should be located at C7 instead of C9.
When running the macro with that value in the wrong cell, the rows were inserted in the wrong place.

After moving the "2" from C9 to C7, I created the table.
Then I ran the macro, and the rows were inserted in the right places!
However, the formulas were not copied down to the new rows, and the C & D values still need to be changed after adding the rows.

Thanks!

In my Excel 2003 workbook the formulas copied down OK. Try this amended code, note I've already created the table from the Inert Tab, keep it as a Table for enhanced features

I've checked the file & it works for me. I can't understand your code above

Well apart from the last Column the new table looks the same as your after example. I can't inderstand why you have a formula based on a column header, if I change I2 to =Row(a2) then the result seems to be what you want

Your macro works for your workbook example, but here's an instance where it fails:
(on your uploaded workbook/macro)
CHANGE:
C2:C10
TO:
(blank)
2
(blank)
1
(blank)
(blank)
(blank)
1
(blank)

CHANGE:
D2:D10
TO:

g
t
g
t
g
u
g
u
g

Then run the macro.

Among other things, sometimes it inserts the rows above when they should be below.

*I figured out this is because my repeated requests for the macro to handle zeroes or blank values in Column D were completely ignored/never addressed.

Okay, I'm almost to the finish line with this.. I've been playing with the macro royUK gave me, and it's pretty close to what I need now.

There's just a few (three) small changes I would like to make:

1. I would like the macro to treat blank cells in column C as if they contain the numeral "1". (so exactly one line is inserted). Is there any way to do this?
(I've highlighted that part of the code in bold text.)

After The Rows Are Inserted & Column A Is AutoFilled:
2. I would like just the values from Column A pasted to Column B.

3. I would like any values of "t" or "u" in Column D changed to a value of "e".

Please Login or Register  to view this content.

Any help would be much appreciated!

Thanks..

[QUOTE
*I figured out this is because my repeated requests for the macro to handle zeroes or blank values in Column D were completely ignored/never addressed.[/QUOTE]

I was addressing the initial request to sort that out first. I'm not actually on your payroll!