CODEWITHSUNDEEP
vbaexceldelete-rowworksheet
gbra
gbra

Reputation: 53

Delete rows on multiple worksheets using userform 'delete' button

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. I would like to modify the code so that when a user is added or deleted, it checks each worksheet and modifies the 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
    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 get a

'Next without For' error.

I don't understand why this is happening as I thought that is what I was doing in this section:

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
Next

Any advice will be greatly appreciated.

(I am hoping that once I have solved the deletion problem, I will be able to modify the solution to add rows when adding a new pupil!)

Upvotes: 2

Views: 252

Answers (1)

Vityata
Vityata

Reputation: 43585

Add End With:

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

before the Next.

As a best practice, it is probably a better idea to use ThisWorkbook.Worksheets, because it loops only the Worksheets and avoids the Charts, if they are available.

Upvotes: 1

Related Questions