Add and Remove Rows based on Cells

Hello,

I'm creating a weekly presentation spreadsheet for a military unit that will change hands frequently and I can't rely on users to know how to right click and add or delete a row.

In order to uncomplicate the procedure for users, I've added two cells - one for a member's rank, and the other for their last name. The list is required to be in rank order and then alphabetical (I realize that it is not this way in the attached image). When a user enters a name that does not exist in the list and the user clicks "Add Member", I need a macro to add a row in the correct place with that member's rank and name. Alternatively, when an existing member's name and rank are entered and "Remove Member" is clicked, I need a macro that will delete that row. Any help with this is greately appreciated - I'm way in over my head on this one.

Edit: I've included a sample worksheet with examples as suggested by AlphaFrog. If any more clarification is required, feel free to ask.
Edit 2: Uploaded .xlsm as per AliGW.

Rank order from lowest to highest is:
AB, Amn, A1C, SrA, SSgt, TSgt, MSgt, SMSgt, CMSgt, 2nd Lt, 1st Lt, Capt, Maj, Lt Col, Col

Capture.PNG

I would be best if you attach a sample workbook. Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

Thanks AlphaFrog. I've edited my original post to include the example workbook.

The attached workbook is sans macros - it's an .xlsx file. You need to provide the macro-enabled (.xlsm) version.

The workbook didn't yet contain any macros, so I hadn't saved it as such, but good catch - thanks for pointing that out! Fixed

OK, understood. I took your opening post to mean that you needed the two macros tweaking.

Try the attached file.

I did change your table a little. It made coding the macros easier:

• The Rank and Name are separated into two columns.
• The Indirect functions in the formulas were removed.

No file attached ...