Deleting listbox list, from listbox and source

[blaker]

I would like to double click on one of the items / rows of data in the list box and delete that item in the list box as well as the source in the spreadsheet. this is multi column, the sheet is Market and the Range is "AA7:aq26" when the listbox data is loaded. I have the below code but it only deletes the row from the list box not the source?

Private Sub ListBox3_DblClick(ByVal Cancel As MSForms.ReturnBoolean)
Dim x As Long
Dim OriginalCount As Long

'Store original ListBox count
OriginalCount = ListBox3.ListCount



'Delete selected line items
For x = OriginalCount - 1 To 0 Step -1
If ListBox3.Selected(x) = True Then ListBox3.RemoveItem x
Next x

see below the form open in front of the spreadsheet sorcedata for the listbox.
Thanks!
Blake

 

[DanteAmor]

Okay, let's try to delete the record using column AK as a key.

Update your ListBox3_DblClick code with the following:

Private Sub ListBox3_DblClick(ByVal Cancel As MSForms.ReturnBoolean)
  Dim x As Long
  Dim OriginalCount As Long
  Dim f As Range
  Dim sh As Worksheet
  Dim customer As String
  
  Set sh = Sheets("Market")
  'Delete selected line items
  For x = ListBox3.ListCount - 1 To 0 Step -1
    If ListBox3.Selected(x) = True Then
      customer = ListBox3.List(x, 10)
      If customer <> "" Then
        Set f = sh.Range("AK:AK").Find(customer, , xlValues, xlWhole, , , False)
        If Not f Is Nothing Then
          f.EntireRow.Delete
          ListBox3.RemoveItem x
        End If
      End If
    End If
  Next x
End Sub

 

[blaker]

Hey this works perfectly! however I have formulas in the adjacent cells that calculate cost and don't really want to delete the entire row, would there be a way to in the selected row delete the values in "Y:AD", "AF:AM" and "AO" and the shift cells up...
is there a way to do this? never the less I really appreciate the code you have provided so far!!!
Thanks!
Blake

 

[DanteAmor]

delete the values in "Y:AD", "AF:AM" and "AO" and the shift cells up

Try this:

Private Sub ListBox3_DblClick(ByVal Cancel As MSForms.ReturnBoolean)
  Dim x As Long
  Dim OriginalCount As Long
  Dim f As Range
  Dim sh As Worksheet
  Dim customer As String
  
  Set sh = Sheets("Market")
  'Delete selected line items
  For x = ListBox3.ListCount - 1 To 0 Step -1
    If ListBox3.Selected(x) = True Then
      customer = ListBox3.List(x, 10)
      If customer <> "" Then
        Set f = sh.Range("AK:AK").Find(customer, , xlValues, xlWhole, , , False)
        If Not f Is Nothing Then
          sh.Range("Y" & f.Row & ":AD" & f.Row & _
                   ", AF" & f.Row & ":AM" & f.Row & _
                   ", AO" & f.Row).Delete shift:=xlUp
          ListBox3.RemoveItem x
        End If
      End If
    End If
  Next x
End Sub

 

[blaker]

Dim x As Long Dim OriginalCount As Long Dim f As Range Dim sh As Worksheet Dim customer As String Set sh = Sheets("Market") 'Delete selected line items For x = ListBox3.ListCount - 1 To 0 Step -1 If ListBox3.Selected(x) = True Then customer = ListBox3.List(x, 10) If customer <> "" Then Set f = sh.Range("AK:AK").Find(customer, , xlValues, xlWhole, , , False) If Not f Is Nothing Then sh.Range("Y" & f.Row & ":AD" & f.Row & _ ", AF" & f.Row & ":AM" & f.Row & _ ", AO" & f.Row).Delete shift:=xlUp ListBox3.RemoveItem x End If End If End If Next x

This works perfect!!!

Thanks so much!!

 

[DanteAmor]

I'm glad to help you. Thanks for the feedback.