Excel VBA UserForm: Delete a Record by ID with Code

To delete a record from an Excel VBA UserForm, identify it by a unique ID, ask the user to confirm, then delete the matching worksheet row. The example below assumes IDs are in column A of a worksheet named Records, with headers in row 1; save a copy of the workbook before running the macro because VBA deletions cannot be undone with Ctrl+Z.

How UserForm record deletion works

A UserForm is an interface for entering, viewing, or changing workbook data. A delete button does not automatically know which worksheet record the user means: its click event needs to locate the record, check that it is safe to remove, and then perform the deletion. The most reliable approach is to search for a stable, unique identifier such as a customer number or invoice ID.

Deleting by the record’s current row number is risky. Rows move when someone sorts, inserts, or deletes data, so a stored row number can point to a different person later. An ID remains associated with the record even when its position changes. After deletion, refresh any list or controls on the form so they no longer display the removed record.

Prepare the worksheet and UserForm

For the code below, arrange the data on a worksheet named Records. Put a unique ID in column A, use row 1 for column headings, and begin records in row 2. For example, A2 might contain C1042, while the remaining columns hold a name, email address, or other fields. The workbook should be saved as an Excel Macro-Enabled Workbook (.xlsm).

Add a text box to the UserForm and set its (Name) property to txtID. It should contain the ID of the record currently selected for deletion.
Add a command button and set its (Name) property to cmdDelete. Its caption can be Delete Record.
Keep IDs unique. If two rows share an ID, the sample code deletes the first matching row it encounters, which may not be the intended record.
Store IDs with leading zeros as text. A numeric value such as 0017 can be stored by Excel as 17, making it impossible for code to recover the original formatting reliably.

These names are control names used by VBA, not necessarily the text shown to users. Set them in the UserForm designer’s Properties window. The form may display other fields too, but the delete routine needs a dependable way to identify the selected record.

VBA code for the Delete button

In the UserForm’s code module, add this event procedure. It checks for a blank ID, searches column A, requests confirmation, and deletes the matching worksheet row only if the user chooses Yes.

Private Sub cmdDelete_Click()
    Dim ws As Worksheet
    Dim lastRow As Long
    Dim rowNum As Long
    Dim recordID As String
    Dim cellID As Variant
    Dim answer As VbMsgBoxResult

    ' Read the selected ID and reject an empty value.
    recordID = Trim$(Me.txtID.Value)
    If Len(recordID) = 0 Then
        MsgBox "Select a record before deleting.", vbExclamation
        Exit Sub
    End If

    ' Find the final used row in the ID column.
    Set ws = ThisWorkbook.Worksheets("Records")
    lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row

    ' Compare IDs as text so numeric and text IDs can be matched.
    For rowNum = 2 To lastRow
        cellID = ws.Cells(rowNum, "A").Value2
        If Not IsError(cellID) Then
            If StrComp(Trim$(CStr(cellID)), recordID, vbTextCompare) = 0 Then
                answer = MsgBox("Delete record ID " & recordID & "?", _
                    vbYesNo + vbQuestion + vbDefaultButton2, "Confirm deletion")

                If answer = vbYes Then
                    ws.Rows(rowNum).Delete
                    Me.txtID.Value = vbNullString
                    MsgBox "The record was deleted.", vbInformation
                End If
                Exit Sub
            End If
        End If
    Next rowNum

    MsgBox "No record was found with ID " & recordID & ".", vbInformation
End Sub

The search begins at row 2 so it cannot match the heading. It compares the trimmed ID without distinguishing uppercase from lowercase, so an ID such as ab12 matches AB12. If capitalization is meaningful in your ID scheme, change vbTextCompare to vbBinaryCompare. The code also ignores error values in the ID cells rather than trying to convert them to text.

Where to put the code and how to run it

Open the workbook in the desktop version of Excel. On Windows, press Alt+F11 to open the Visual Basic Editor. On a Mac, use Developer > Visual Basic; the keyboard shortcut can depend on the keyboard and function-key settings.
In the Project Explorer, locate the UserForm, then open its code window. Choose the form’s cmdDelete control and its Click event, or paste the complete procedure into the form’s code module.
Check that the worksheet tab is named Records and that the control is named txtID. Edit those names in the code if your workbook uses different names.
Save the file as an .xlsm workbook. Return to Excel, open the form, select a record, and click the delete button. Confirm that the prompt identifies the intended ID before choosing Yes.

Use macros only in workbooks you trust, and enable them only when appropriate for your organization’s security settings. Excel for the web can open an .xlsm file, but it cannot run VBA macros or display a VBA UserForm; use desktop Excel for this workflow. Excel for Mac supports VBA, although some Windows-specific controls and automation features are not available there.

What the deletion code removes—and what it assumes

The line ws.Rows(rowNum).Delete removes the entire worksheet row, not just the values in the ID column. That is suitable when the worksheet is a dedicated record list with one record per row. It also shifts rows below the deleted record upward, which is why the routine locates the row from the ID each time instead of relying on a fixed row number.

This whole-row approach needs reconsideration if the same worksheet row contains unrelated information in other columns or alongside the record table. If the data is an Excel Table, deleting the matching table row with the table’s ListRows(index).Delete method can be a better fit: it removes a table record without treating the entire worksheet row as the record. The table version must first find the ID’s position within the table, rather than use the worksheet row number as the table-list index.

The example assumes column A is populated for every record and that row 1 is the header. A blank ID inside the data, IDs stored inconsistently, or a different worksheet name can cause a failed or incorrect match. It also searches hidden rows, including rows hidden by a filter. If users expect the button to delete only the visible selection, make the selected row or selected record explicit and validate that selection before deleting.

Safety checks and common problems

Protect against accidental deletion. The confirmation prompt defaults to No. For higher-impact data, consider requiring a second identifier or moving records to an archive sheet instead of deleting them immediately.
Keep a recoverable copy. Ctrl+Z does not reverse VBA changes. Save a separate copy or use a tested backup process before running deletion macros on important records.
Check worksheet protection. A protected sheet may prevent row deletion. Unprotect it through an authorized workflow before the macro runs, then restore protection; do not silently remove protection without considering the workbook’s controls.
Refresh the form after deletion. If a ListBox or other control is populated from the worksheet, reload its rows and clear or select another record after the delete succeeds. Otherwise, the form can continue to show a record that no longer exists.
Check formulas and references. Deleting a row can affect formulas, charts, named ranges, and other sheets that refer to the data. Test the behavior on a copy, particularly when the workbook has dependent calculations.
Investigate a “not found” message. Confirm that the ID is actually in column A, that the worksheet tab name matches, and that the text box contains the ID rather than a display label. Leading spaces, numeric IDs formatted with zeros, and duplicate keys can also complicate matching.

For a larger application, add error handling around worksheet access and deletion, and consider logging who deleted a record and when. A simple confirmation dialog is useful but is not an audit trail. If records have legal, business, or retention requirements, check the applicable policy before building permanent deletion into a form.

Alternatives to permanently deleting a row

Sometimes the safer action is to mark a record inactive rather than remove it. A Status column can be changed to Inactive, or a DeletedDate field can record when a record was retired. The form can hide inactive records by default while preserving history for reports and recovery. This is a data-design choice, not a built-in Excel requirement, and it needs clear rules so archived records are not mistaken for current ones.

Another option is to move the record to an archive worksheet before removing it from the active list. That requires code to copy the complete record, verify the copy, and then delete the original; copying alone does not guarantee the archive is complete. For a workbook shared by many people or holding substantial operational data, a database may provide better permissions, audit history, and concurrent editing than a worksheet-backed UserForm.

Frequently Asked Questions

How do I delete the selected record from a VBA UserForm?
Put the deletion routine in the form’s button Click event. Pass the selected record’s unique ID to the routine, locate that ID in the worksheet or table, confirm the action, and then delete the matching record.

Can I delete a row by its row number?
Yes, but a row number can become stale after sorting or inserting rows. Use a unique ID to find the current row immediately before deletion; use a row number only when it is freshly obtained and tied to the current selection.

Why does my delete button say the record was not found?
Check the worksheet name, ID column, header row, and the value in the form’s text box. Also check for duplicate or inconsistently formatted IDs. A number displayed with leading zeros may not be stored as the same text value.

Can I undo a VBA deletion with Ctrl+Z?
No. Excel’s normal Undo history does not reverse changes made by a VBA macro. Save a backup copy before running the routine, or design the workflow to archive or mark records inactive instead.

Will the UserForm macro work in Excel for the web?
No. Excel for the web does not run VBA or show VBA UserForms. Open the macro-enabled workbook in desktop Excel for Windows or Mac, subject to any platform-specific control limitations.

Try it: the Column Letter to Number: aA is 27, XFD is 16,384. Converts both ways.

More in VBA & macros

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.