Using Macros to remove a name from a list

[Pat]

I own a small business with roughly 45 employees (mostly teenagers). There's
lots of turnover. I've got a spreadsheet that I use to track tips according
to the role and shift that our staff works. My manager has the ability to
select the staff member who earned the tip from an in-cell drop down list
(using DV). Here's my question.

I want to make it really easy for my managers to add or delete staff members
from the drop down list. I've created a separate worksheet with two
sections: "add staff member" and "delete staff member". When they want to
add a staff member, they put the name(s) into a predefined box and click the
"add staff member" button I created. In the background, a macro copies the
new staff member names, pastes them at the bottom of the source list for the
drop down, and then sorts the names in alphabetical order. It works well.

I can't figure out the right approach for the "delete staff member" process.
I've tried in column B (right next to the source list) using the OR function
to return a true or false when comparing the names in the source list with
the name(s) in the "delete staff member" box. If the OR function returns
"False" (meaning that the name doesn't match one of the names in the delete
section), then I copy the name in the source list to column C. But if it
returns "True", then I'm returning "" as the value. My problem with this
approach is I can't figure out how to get excel to delete the blank cells and
move the non-blank cells up.

If there's a better way to do it, I'm all ears. If this is the right
approach and there's a way to automatically delete the blank cells and get
the non-blanks to move up, I'd welcome that as well.

Pat

 

[Don Guillett]

If you have a defined name for your list (as you should), then simply
deleting the row that the name is on should do it for you.
If desired, send your file to my address below along with this msg and
a clear explanation of what you want and before/after examples.

 

[Jim Cone]

Sorting a list, moves blanks to the bottom.
--
Jim Cone
Portland, Oregon USA



"Pat" <[email protected]>
wrote in message
I own a small business with roughly 45 employees (mostly teenagers). There's
lots of turnover. I've got a spreadsheet that I use to track tips according
to the role and shift that our staff works. My manager has the ability to
select the staff member who earned the tip from an in-cell drop down list
(using DV). Here's my question.

I want to make it really easy for my managers to add or delete staff members
from the drop down list. I've created a separate worksheet with two
sections: "add staff member" and "delete staff member". When they want to
add a staff member, they put the name(s) into a predefined box and click the
"add staff member" button I created. In the background, a macro copies the
new staff member names, pastes them at the bottom of the source list for the
drop down, and then sorts the names in alphabetical order. It works well.

I can't figure out the right approach for the "delete staff member" process.
I've tried in column B (right next to the source list) using the OR function
to return a true or false when comparing the names in the source list with
the name(s) in the "delete staff member" box. If the OR function returns
"False" (meaning that the name doesn't match one of the names in the delete
section), then I copy the name in the source list to column C. But if it
returns "True", then I'm returning "" as the value. My problem with this
approach is I can't figure out how to get excel to delete the blank cells and
move the non-blank cells up.

If there's a better way to do it, I'm all ears. If this is the right
approach and there's a way to automatically delete the blank cells and get
the non-blanks to move up, I'd welcome that as well.

Pat

 

[Shane Devenshire]

Hi,

The manual command to delete the cell and move the other ones up is Edit,
Delete, Shift cells up, of Press Ctrl+- (control minus)

The macro line for this is

Selection.Delete Shift:=xlUp
or
ActiveCell.Delete Shift:=xlUp
or
Range("A5").Delete Shift:=xlUp

(xlUp is XL not X1)