I'd like to create a button that will clear completed jobs and move active jobs up when pressed. Column E is a drop down list where either "Complete" or "Active" can be selected. If "Complete" is selected, I'd like the contents in column E, F and H of that row to be cleared. If "Active" is selected, I'd like the contents in column E, F and H of that row to be cut and pasted the number of completed rows above. So if the first two jobs were completed on Machine 1, the remaining 3 "Active" jobs would move up two rows. Each machine contains 5 jobs and there are two blank rows between each machine. It is crucial that the contents in column G are not cleared as well as any content in the columns to the right of column H. Any ideas how this could be accomplished?
| Machine | Job Status (Column E) | Job Number (Column F) | Job Name (Column G) | Operation (Column H) |
|---|---|---|---|---|
| Machine 1 | Complete | 1111 | Job 1 | OP10 |
| Machine 1 | Complete | 2222 | Job 2 | OP30 |
| Machine 1 | Active | 3333 | Job 3 | OP50 |
| Machine 1 | Active | 4444 | Job 4 | OP30 |
| Machine 1 | Active | 5555 | Job 5 | OP40 |
| Machine 2 | Complete | 6666 | Job 1 | OP40 |
| Machine 2 | Complete | 7777 | Job 2 | OP10 |
| Machine 2 | Complete | 8888 | Job 3 | OP30 |
| Machine 2 | Active | 9999 | Job 4 | OP30 |
| Machine 2 | Active | 0000 | Job 5 | OP10 |
| Machine 3 | Complete | 1313 | Job 1 | OP50 |
| Machine 3 | Active | 1414 | Job 2 | OP10 |
| Machine 3 | Active | 1515 | Job 3 | OP10 |
| Machine 3 | Active | 1616 | Job 4 | OP20 |
| Machine 3 | Active | 1717 | Job 5 | OP10 |
