Excel: Advice on using VBA to delete cells on a worksheet


Hi to all I am a novice at VBA and what I have done in the past has been by trial and error Usually I succeed but I am totally stumped by the following. Can anyone help? I have a workbook that contains a sheet called “001 Help Sheet”. This sheets contains a list of employees in three columns F, G and H employee number (F), name (G) and department (H) in each column respectively. I have a user form to enter and add new employees to the list. This works fine. I want a user form to delete an employee. I have set the user form up. The user simply enters the number of the employee to delete in a text box called tbEmpNumberDelete. The command buttons on the form are “OK” named cbOK and cancel, named cbCancel. The code for cbOK is as follows: Private Sub cbOK_Click() 'declare the variables Dim c As Range, emplist As Range Dim lastrow As Long Dim Answer As String Answer = MsgBox("Are you sure you wish to delete employee " & tbEmpNumberDelete.Value & "?", vbYesNo, "Confirm") If Answer = vbNo Then Unload Me Exit Sub Else Application.ScreenUpdating = False lastrow = Worksheets("001 Help Sheet").Range("F" & Rows.Count).End(xlUp).Row Set emplist = Worksheets("001 Help Sheet").Range("F6:F" & lastrow) 'find the value in the range For Each c In emplist If c.Value = tbEmpNumberDelete.Value Then 'delete c.Offset(0, 3).delete Shift:=xlUp End If Next c End If 'turn on screen updating Application.ScreenUpdating = True Unload Me End Sub The list of employees starts in F6. When I run this everything is fine until the user confirms that he does wish to delete the employee and then nothing whatsoever happens. Nothing is deleted. I am sure this is something stupid on my part and obvious – but not to me. Can anyone tell me what I have done wrong?


This question generated 18 answers. To proceed to the answers, click here.

This thread is current as of March 19, 2017.


For more resources for Microsoft Excel