Sorting data validation with VBA

[ddoyle67]

I am pretty new to using macros in my workbook to try and speed up using it. I have a data validation list (C2:C97) called "Description" that has 66 permanent items (C2:C67) that are alphabetized in it and another 30 items (C68:C97) that are added to the list via input on another sheet. When those 30 items don't have an item in the cells, they do have a formula, which returns a 0. What I would like to do is have that group(C2:C97) to automatically re-alphabetize its self when one of those 30 blank cells gets new data but to ignore the blank cells and to keep them at the end of the list. I have a code to alphabetize the list but its only working manually and it puts all the blank or 0 cells at the top. Any help with writing this code would be appreciated. Thanks in advance

 

[ddoyle67]

I will try this tonight when I get home and let you know, never have time to play with it during the day.

All the info for C77 "lockers" would get sorted into the permanent section. A new formula would end up in cell C77 after the sort since the order of those formulas/Cells in C68:C97 doesn't matter, what matters is where the formula(='Calculator'!BJ60, Cell location changes for each row) in those cells is looking to on the other worksheet to get the info to populate it, that data will remain fixed in the cell on the other worksheet

 

[ddoyle67]

I put the code in and inserted a new column so it had a empty D column next to it and all it did was delete the first 2 items in the list I put a test lockers in C81 and it didn't move.

 

[DanteAmor]

Try the file that you sent me. In column C you will have the result

 

[ddoyle67]

Thanks, it worked when I put it in the first sheet I sent how ever it had to be run manually, not automatic like I would like. To run it manually would take more time than scrolling down to find in in the lower half of the validation list. So if it worked in that sheet why isn't it working in my full workbook?

 

[DanteAmor]

I understand that you will need, in addition to executing the macro automatically, other situations that I still understand, but I want you to review the result and tell me if it is what you need and we will improve it.

 

[ddoyle67]

It works in my workbook. I had forgotten to change the sheet name when I put it into the sheet in my workbook. still new at this. So now all I need is to get it to run automatically when I new item is entered in C68:C97. ​