Excel Macro to Add Line while Copying Formats/Formulas

[muckem333]

First time poster here,

I need help writing a macro which I will link to a button. Here is what I'm looking for:

I have 4 columns of data starting in rown B29 and ending in G29 (one of the columns is actually 3 merged columns which is populated by a dropdown list). 2 of the columns have formulas.

I would like the user to click the button, which will add a blank row with all the formatting from above (including the same drop down list and formulas). However, I would like to be able to add multiple lines and have the new line always appear at the end of the list.

As if that weren't complicated enough. I want to add another button to act as a 'reset' button which will delete the added rows, leaving me with the original blank row.

I tried recording my own macro but that doesn't work because the merged cells un-merge as the new row is added. And I can't get the new row to always appear at the bottom if I add more than one.

Is that enough info?

Brian

[JBeaucaire]

Except for the DELETE part, everything you're describing is covered by Excel's built-in LIST feature.

1) Highlight your range of data and the header row above it and press CTRL-L. Follow the prompts to properly defines the list borders.

2) Now Excel has bounded your data with blue border. Click in the data set and Excel offers you an expansion row below the bottom row.

3) If you type anything into one of the cells in this expansion row (choose a cell that won't have a formula in it), then Excel will immediately do all the things you want...copy down formatting, validation, etc, from the row above.

The LIST function is a very useful tool once you start using it.