How to delete row from excel sheet using command button and listbox selection

Question

I have written the following code to delete a row from an excel table, and update the listbox when i click a commandbutton("Delete").

the code executes and says "row deleted successfully." but the data is not actually removed from the excel table or the listbox.

The code i am using is:

'Delete Function to remove row from Excel Table and Listbox

Private Sub CommandButton29_Click()

    Dim ws7 As Worksheet

    Dim foundCell As Range

    Dim searchvalue As Variant
    
    searchvalue = ListBox4.Value
    
    Set ws7 = worksheets(7)
    
    ' Find the cell with the search value in column A
    Set foundCell = ws7.Columns(1).Find(searchvalue)

    ' Check if the value was found
    If foundCell = searchvalue Then
        ' Delete the entire row of the found cell
        foundCell.EntireRow.Delete
        MsgBox "Row deleted successfully.", vbInformation
    Else
        MsgBox "Value not found.", vbExclamation
    End If
End Sub

any help is greatly appreciated. I just started self learning programming a month ago.

I tried modifying the code a few ways with no success

Answer 1

I think it looks like there might be an issue with the way you are checking if the value was found. The Find method returns a Range object, not a single value. You should use the Not foundCell Is Nothing condition to check if the value was found.

Answer 2

try this code :

Private Sub CommandButton29_Click()

    Dim ws7 As Worksheet

    Dim foundCell As Range

    Dim searchvalue As Variant


    searchvalue = ListBox4.Value

    Set ws7 = Worksheets(7)

    ' Find the cell with the search value in column A

    Set foundCell = ws7.Columns(1).Find(searchvalue)

    ' Check if the value was found

    If Not foundCell Is Nothing Then

        ' Delete the entire row of the found cell

        foundCell.EntireRow.Delete

        MsgBox "Row deleted successfully.", vbInformation
    Else

        MsgBox "Value not found.", vbExclamation

    End If
End Sub