Quickly move cell contents in and out of a named range using add/remove btn: best method?

[papazulu]

Here is what I have to create. I've been given the task of finding the easiest and most elegant way to add and remove existing items from a list into and out of a separate named range.
I should add that we created the range with each cell having a drop down menu but got rid of it because it was too slow to do it that way.

Here is a my clunky macro solution so far. There is a list of about 20 cell tower site types in column A. In column C I created a named range that is used in a certain formula.
I created in column B a series of 'Add' buttons with an assigned macro to each that copies and pastes the content from column A (in same row) into the range in column C.
To remove the content from the range one has to manually delete it, just the contents of course.

This works OK but it seems like a clunky makeshift way to do it (especially with all those buttons and redundant macros) and I was just wondering if there were not some more
standard elegant solution for this kind of action.

One thing I was considering was how to make a single add and single remove button that moves a selected item upon selection.

Thank you for any suggestions.

[Greg M]

Hi there,

Take a look at the attached workbook and see if I've understood your requirements correctly.

It uses the following code which is called from a single Add/Remove button:

Please Login or Register  to view this content.

Hope this helps - please let me know how you get on.

Regards,

Greg M

[papazulu]

Outstanding and thank you! Now I want to study your code for personal enrichment and then see if I can tweak it so that I can move multiple selected items with the button rather than having to select just one then 'move' repeatedly.

Again this is great!

[Greg M]

Hi again,

Many thanks for your feedback - glad I was able to help.

Feel free to ask if you need any further information.

Best regards,

Greg M

[papazulu]

Hi Greg! A few days ago (as per my learning of programming at the moment) I was looking at the right column output of the program above you graciously shared, and I was thinking that as a learning activity that I would try to write a program that collapses the entries in a column that have empty cells between them and consolidates them at the top of the column while keeping them in the same order. While Excel probably has a feature that can do this I purposely didn't check so it could enhance the feeling that I was creating something new and useful (this is a 'head' thing when I'm learning something new). Anyway I wrote a collapse-data program without any direct help just using the basic VBA techniques I've learned so far. If you want, take a look at it and comment generally. There is a little more I could do to make it more robust and reusable but I'm moving on to something new at this point.

Please Login or Register  to view this content.

This is actually the first somewhat 'meaty' program I've ever written. I worked security in the oil field till it 'tanked' last year and my security company folded. I'm using getting laid off as an opportunity to pursue a more lucrative career path. The caveat is that I'm in my mid-40s but oh well. Over the past few months I've also managed to teach myself through a semester of C++. So my next project is to come up with some C++ problems to solve that are comparable in skill level to the VBA one I posted above. The main skills that I've picked up since getting laid off are Excel Basic plus some advanced, Excel VBA, C++, and AutoCAD.

Cheers. You rock!