Condition Based Cell Removal

Question

I have a list of employees in column A and their status (selected from the drop-down menu, "Active" or "Inactive") in column B. They are in a sheet called "List of employees".

When the employee status is set to Inactive, I want to automatically cut and paste the employee on another sheet. Another sheet is called "Misc"

If this cannot be done automatically when the status is set to “Inactive”, perhaps I can set a button that will call the VBA commands on this sheet to clear all inactive employees from the list and move them to another sheet.

Answer 1

I'm more hungry for a reputation than Ben M, so I'll send you the code. :) You should still, of course, consult and start reading good books.

The following may use some tweaking, but should be a good starting point. If, as you wrote, you want Excel to automatically move the name and status of the employee as soon as an inactive choice is made, this should do the trick:

Private Sub Worksheet_Change(ByVal Target As Range) Application.EnableEvents = False ' Only react to edits in Column B: ' If Not Intersect(Target, Sheets("Employee List").Range("B:B")) Is Nothing Then ' Dont do anything if > 1 cell was just changed: ' If Target.Cells.Count = 1 Then ' Only make the change if the new value in Col B is "inactive": ' If Target.Value = "Inactive" Then ' Find the next available cell on the Misc sheet for a name: ' Dim nextRange As Range Set nextRange = Sheets("Misc").Range("A65536").End(xlUp).Offset(1, 0) ' Cut the employee name and status and paste onto the Misc sheet: ' Range(Target, Target.Offset(0, -1)).Cut Sheets("Misc").Paste Destination:=Sheets("Misc").Range(nextRange.Address) End If End If End If Application.EnableEvents = True End Sub 

Note that whenever you write code with an event, you probably need to disable events so that Excel does not fall into any endless loop.

Answer 2

Once the best ways to get started in Excel VBA is to write macros and look at the code that they produce. This way you will see how you can manipulate objects in Excel using VBA code.

Also consider getting a VBA Developer Handbook . It is based on older versions of Office, but VBA has not changed much (if at all) in recent versions of Office views, so it is still well-read.

Learn basic things like creating objects, loops, conditional logic, string concatenation, etc., and it will take you a long way.

---

For your current problem, what you can do is write a cut and paste macro to move and see what code it produces. Then see if you can understand how to change this code to suit your needs.

Go back to the stack overflow and ask very specific questions if you are stuck, and that this way to get a good answer. For example, you might say, "How do I scroll my cell range to apply this copy and paste?"