Deleting rows on multiple worksheets

Question

Following on from a previous question I asked today - I have modified code written by Roy Cox (Thank you for saving me SO much time!) to create a userform to add, modify and delete users' details in an analysis tool I am creating.

It works perfectly when working with user data on a single worksheet.

Selecting a user and clicking 'delete' deletes their user data on the worksheet. I have modified the code so that when a user is added or deleted, it should check each worksheet and adds or deletes rows accordingly.

This is the code to delete the pupil data on a single sheet:

Private Sub cmbDelete_Click()

Dim msgResponse As String    'confirm delete

Application.ScreenUpdating = False

'get user confirmation
msgResponse = MsgBox("This will delete the selected record. Continue?", _
    vbCritical + vbYesNo, "Delete Entry")
Select Case msgResponse    'action dependent on response

Case vbYes
'c has been selected by Find button on UserForm
Set c = ActiveCell
    c.EntireRow.Delete    'remove entry by deleting row

'restore form settings
With Me
    .cmbAmend.Enabled = False    'prevent accidental use
    .cmbDelete.Enabled = False    'prevent accidental use
    .cmbAdd.Enabled = True    'restore use
    'clear form
    Call ClearControls
End With

Case vbNo
Exit Sub    'cancelled
End Select

Application.ScreenUpdating = True

End Sub

I have tried modifying it to delete the user data on each worksheet, as follows:

Private Sub cmbDelete_Click()

Dim Sh As Worksheet
Dim msgResponse As String    'confirm delete

Application.ScreenUpdating = False

'get user confirmation
msgResponse = MsgBox("This will delete the selected record. Continue?", _
    vbCritical + vbYesNo, "Delete Entry")
Select Case msgResponse    'action dependent on response

Case vbYes
For Each Sh In ThisWorkbook.Sheets
With Sh.UsedRange
    'c has been selected by Find button
    Set c = ActiveCell
        c.EntireRow.Delete    'remove entry by deleting row
End With
Next

'restore form settings
With Me
    .cmbAmend.Enabled = False    'prevent accidental use
    .cmbDelete.Enabled = False    'prevent accidental use
    .cmbAdd.Enabled = True    'restore use
    'clear form
    Call ClearControls
End With

Case vbNo
Exit Sub    'cancelled
End Select

Application.ScreenUpdating = True

End Sub

but this version deletes the user's data and the data on the 4 rows beneath them. It does not delete data from the next worksheet at all.

Can anyone offer any advice please?

Answer 1

change:

Case vbYes
    For Each Sh In ThisWorkbook.Sheets
    With Sh.UsedRange
        'c has been selected by Find button
        Set c = ActiveCell
            c.EntireRow.Delete    'remove entry by deleting row
    End With
    Next

to:

Case vbYes
    Dim l As Long
    l = ActiveCell.Row 'store currently active cell row
    For Each Sh In ThisWorkbook.Worksheets
        Sh.Rows(l).Delete
    Next

---

should you ever be interested in knowing why your previous code didn't work:

1)

Set c = ActiveCell

would set c to the currently active cell, i.e. the cell your "Find" button selected in the currently active sheet

2) while

c.EntireRow.Delete

would always, quite unsurprisingly, delete c entire row, i.e. the same row in the sheet where c has been found in, since nobody is setting c again and point to another sheet range.

and simply looping through Sheets collection doesn't change the Active sheet