VBA Remove Item in a Listbox by selecting

[john4765]

I have a user form with Delete button that deletes the selected item inside my listbox whenever it is clicked. And every time there is a deleted item there, the deleted item should also be removed in my Worksheet range. How can i delete a selected row in the listbox?
<code style="margin: 0px; padding: 0px; border: 0px; font-style: inherit; font-variant: inherit; font-weight: inherit; font-stretch: inherit; line-height: inherit; font-family: Consolas, Menlo, Monaco, "Lucida Console", "Liberation Mono", "DejaVu Sans Mono", "Bitstream Vera Sans Mono", "Courier New", monospace, sans-serif; vertical-align: baseline; box-sizing: inherit; white-space: inherit;">Private Sub CmdDelete_Click()

Dim i As Integer
For i = 1 To Range("A1000000").End(xlUp).Row
If Cells(i, 1) = Listbox1.List(Listbox1.ListIndex) Then
Rows(i).Select
Selection.Delete
End If
Next i
End Sub</code>

 

[Yongle]

Assuming that the listbox is popuated from the same range as deleted ...

- delete the item from the range
- clear the listbox
- repopulate from the range

Like so...

Private Sub CmdDelete_Click()
    On Error Resume Next
    Range("A:A").Find(ListBox1.Value).Delete Shift:=xlUp
    ListBox1.Clear
    ListBox1.List = Range("A1", Range("A" & Rows.Count).End(xlUp)).Value
End Sub

Private Sub UserForm_Initialize()
    ListBox1.List = Range("A1", Range("A" & Rows.Count).End(xlUp)).Value
End Sub

NOTE
- clear Textbox RowSource property (if used) before using above code to avoid a conflict

 

[Yongle]

in above code this deletes only the cell

   Range("A:A").Find(ListBox1.Value).Delete Shift:=xlUp

this would delete the entire row

   Range("A:A").Find(ListBox1.Value).EntireRow.Delete

 

[john4765]

I tried this. But having Run-time error - 2147467259 unspecified error
Private Sub CmdDelete_Click()
On Error Resume Next
Range("A:A").Find(Listbox1.Value).EntireRow.Delete
Listbox1.Clear
Listbox1.List = Range("A1", Range("A" & Rows.Count).End(xlUp)).Value
End Sub

 

[john4765]

I'm trying to delete an entire selected row in the listbox.
https://www.mrexcel.com/forum/excel...-modifying-deleting-selected-row-listbox.html

 

[Yongle]

I successfully tested with
- a few values in column A in the active sheet
- a NEW userform with ONE command button, and ONE (single column) ListBox

If you do the same ...
ie start with a NEW userform (your error is probably due to an existing setting etc)
and confirm that it is working
and tell me what is different in your userforrm and workbook
... we will take it from there step by step

thanks

 

[john4765]

The Problem is when i delete the records and adding new records in the listbox by default they're adding in second row and they're not showing up in listbox. How can i modify this ?