Add / Delete Row from Named Range

[Gregg Wolin]

I have a budget that will starts off with a single line item which will be a named range (r_budget).

Macro 1: I'd like a macro (triggered by a button) to copy the last row of the r_budget range and insert / paste it to the bottom of the range blanking out the values of certain cells. The pasted row would also include an X in the cell to the left of the leftmost cell that would act as a trigger Macro 2.

Macro 2: Clicking the X in any row will delete the row. This part i can probably handle myself, its the duplication or deletion of the trigger button (X) that I am lost on.

Hopefully the attached sheet is clear as to what I'd like to accomplish.

 

[BobBridges]

About clicking the 'X'; I know of no event that'll respond to clicking on a cell with a mouse. Or rather, there is one, Worksheet_SelectionChange, but it'll be triggered also by the user just using the arrow keys to move the selection across one of the 'X' cells. So I don't think you want that. You could use BeforeDoubleClick; that's probably best.

I was going to go back to the first macro and talk about it alone ("one at a time"), but now I've learned it's time to read the next chapter to my son . Back later this evening.

 

[BobBridges]

Ok, so I gather from looking at your worksheet that if a user clicks on either button, you want to copy the line next to the button — or, if there is more than one line in that area, the bottom line from the area next to that button — and copy it to the bottom of the second area that is below that, inserting an 'X' in the cell to the left of the new row so that a user can later click double-click on that X to remove the line. Right so far?

To do that, your program has to do a few things:

1) It has to detect the upper area. I'm guessing the top, left and right bounds can be hard-coded; if so, it only needs to be sure where the area ends, so it knows which row to copy from.

2) Detect the lower area. Here the left and right boundaries can probably be hard-coded, but it needs to be sure where the lower area starts and where it ends. And there's an extra wrinkle: In the right side of the worksheet (but not the left) there seem to be two lower areas. Which one do you want the new line copied to, in that case?

3) It has to insert the new line, adding it at the bottom of the lower area but before the Total line.

4) The Total has to be adjusted so that the new line is included therein. You might think that'll happen automatically, but it may not; gotta watch that.

I think the rest of it is going to be pretty simple. So let's start here: How will your program know the upper area, so it can find the bottom line of that area? Will the lines always start in row 7? Are columns C through F always the cells that must be copied down? Will the last line in that area always come just before "Total" in C8? Or must we use some other clue? You mentioned naming that range; I haven't fooled with named ranges much, but maybe it's time I learned. Will you have a separate name for each of the two upper areas, then?