Deleting cells based on conditions

Question

I have a list of employees in column A, and their status (chosen from a dropdown menu, "Active" or "Inactive") in column B. They are in a sheet called "Employee List".

When an employee's status is set to "Inactive", I want to cut and paste the employee to another sheet automatically. The other sheet is called "Misc"

If this cannot be done automatically when the status is set to "Inactive", then maybe I can set a button that would invoke the VBA commands in that worksheet to clear all inactive employees from the list and move them to the other sheet.

Answer 1

Once of the best ways to get started in Excel VBA is to record Macros and look at the code they produce. That way you'll see how you can manipulate objects in Excel using VBA code.

Also, consider getting VBA Developer's Handbook. It's based on older versions of Office, but VBA hasn't changed much (if at all) in the last view versions of Office, so it's still a good read.

Learn basic things like object instantiation, loops, conditional logic, string concatenation, etc. and that will take you a long way.

---

For your current problem, what you might do is record a macro of a cut and paste move and see what code it produces. Then see if you can figure out how to modify that code to suit your purposes.

Come back to Stack Overflow and ask very specific questions if you get stuck and that's the bast way to get a good answer. For example, you might say "how do I loop through my range of cells to apply this copy and paste?".

Answer 2

I'm more hungry for reputation than Ben M here so I'll send you some code. :) You should still take his advice of course, and start reading good books.

The following could use some fine-tuning still, but should be a good starting point. If, as you wrote, you want Excel to automatically move the Employee Name and Status as soon as the 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

Notice that whenever you write code against an event, you probably need to disable events so Excel doesn't get into any sort of infinite loop.