Advice on using VBA to delete cells on a worksheet

[Graydjames]

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?

 

[dmt32]

Hi,
You can Delete an Entire Row or an Entire Column but not a single Cell if this is what you are trying to do? You can either clear the Cell contents or Clear All (Formats etc) to leave an empty Cell

Based code posted, suggests that the value to be delete appears more than once in your range? If this is so, try this update to your code & see if helps:

 Private Sub cbOK_Click()
'declare the variables
    Dim c As Range, emplist As Range, DeleteRange As Range
    Dim lastrow As Long
    Dim Answer As VbMsgBoxResult
    
    Answer = MsgBox("Are you sure you wish to delete employee " & tbEmpNumberDelete.Value & "?", vbYesNo, "Confirm")
    
    If Answer = vbYes Then
        
        Application.ScreenUpdating = False
        
        With Worksheets("001 Help Sheet")
            lastrow = .Range("F" & .Rows.Count).End(xlUp).Row
            Set emplist = .Range("F6:F" & lastrow)
        End With
        
'find the value in the range
        For Each c In emplist.Cells
            If c.Value = tbEmpNumberDelete.Value Then
                If DeleteRange Is Nothing Then
                    Set DeleteRange = c
                Else
                    Set DeleteRange = Union(DeleteRange, c)
                End If
            End If
        Next c
    End If
'delete all matched rows in one go
        If Not DeleteRange Is Nothing Then DeleteRange.EntireRow.Delete
'turn on screen updating
        Application.ScreenUpdating = True
        Unload Me
End Sub

If delete value only appears once in your range the better to use Range.Find method in place of for next loop.

Dave

 

[Peter_SSs]

Your method of looping through the cells to find the employee is not the most efficient way, but to answer your question I think that you need to make this change.
Note though that I have not tested this code.

<del>c.Offset(0, 3).delete Shift:=xlUp</del>
c.Resize(1, 3).Delete Shift:=xlUp

Your Offset line of code was taking you right 3 cells from column F, that would be to column I, and deleting that cell.

 

[SQUIDD]

I just set up a sheet and userform, tested what peter suggested, it works fine for me.

Although, only if i used an employee name rather than employee number, so watch out for format issues that may cause the code not to work also.

Dave

 

[Graydjames]

Hi Dave

Thank you for your very quick reply.

So I have a list something like this ( the real list is longer):

001 John Smith Admin
010 Linda Williams Admin
095 Adam Jones Mechanic
100 Will Bloggs Mechanic
105 Harry Johnson Mechanic
094 Paul James Sales
106 Sally Franks Sales

The list is in three columns starting at row 6 - column F contains the number, column G the name and column H the department.

Say Paul James leaves the company. I want to be able to delete cells F11, G11 and H11 and move the cells below them upwards.

Are you saying this cannot be done? Sorry if I am being thick. Each number only appears once in the list. There are no duplicates allowed.

If I can only delete whole rows then I could move the list to its own sheet although it would mean some surgery to the rest oft he workbook.

Thanks again

Graham

 

[Graydjames]

Hi Peter

Thank you for your quick response. I tired this but no luck. I see now what you are saying about my line of code but it still does nothing. I think something is wrong before that line.


Graham

 

[Graydjames]

Peter and Dave

Ah! Yes, the light is dawning. I too can get it to delete the cell with the name if I do it by name and not number. Sop I have two problems then:


1 The is some issue with the formatting of the number so it is not being recognised; and

2 I can only delete a single cell not all three cells in the list.

I can work on the formatting (using name is tricky because the user would need to type it exactly as it is written) so number gives less room for error.

But is there just no way to delete all three cells in column F G and H or is it easier to move the list to its own sheet and remove the entire row.

I must say I most appreciative of the help and the amazing speed with which it comes!

Graham

 

[dmt32]

You cannot Delete single cells, just an entire row or column.

If however this is not what you want to do, one solution maybe to cut & paste the all the rows in columns specified that are below the cells you want to clear & move the remainder up one row.


Dave

 

[Graydjames]

OK folks I think I have cracked it.

I added some extra code and now it does delete all three cells. So if you are interested the code now looks like this:

Private Sub cbOK_Click()
'declare the variables
Dim c As Range, emplist As Range
Dim lastrow As Long
Dim Answer As String
Dim ENumber 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
ENumber = tbEmpNumberDelete.Value
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 = ENumber Then
'delete
c.Resize(1, 3).delete Shift:=xlUp
End If
Next c
End If
'turn on screen updating
Application.ScreenUpdating = True
Unload Me
End Sub

The numbers in the list were formatted thus "00#" (I need those leading noughts for other reasons).

Anyway it is now working exactly as required. I am most grateful for your help.

Graham

 

[SQUIDD]

Glad you got it all working

I would have a suggestion for you though.
As you said a user may write a name wrong.

You could have used a combo box with the row source set to the column/range where your names/numbers are.
This way the user writes nothing and simply selects from the data available.

Regards

Dave