Excel VBA: Help with deleting cells based on conditions

Question

I have a new job (yay!) which includes a chunk of Excel work that I have to do. I am new to Excel programming, so I am planning on picking up a book or two to start learning. But, in the meantime, I already have tasks to do, so I was hoping that someone would be able to help.

I need help programming a little task with VBA. I have a list of employees in column A, and their status (choosen from a dropdown menu, "Active" or "Inactive") in column B. They are in a sheet called "Employee List".

I want to make it so that when an employee's status is set to "Inactive", the employee will be cut and pasted 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 provoke the VBA commands in that worksheet to clear all inactive employees from the list and move them to the other sheet.

Any help or advice would be appreciated. Thanks!

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.