Solved: Deleting data selected in a list box from its source sheet

[pcarmour]

Hi, I have a list box that has multiple rows and 15 columns of data that is sourced from a spread sheet. My question is how can I delete the data selected in the list box from the source sheet once it has been selected and move the remaining data on the spread sheet up.
I am working with Windows Home Premium version 6.1.7601 SP 1 Build7601and Excel version 14.0.6123.5001 (32 bit)

Any help would be much appreciated.

[p45cal]

Is the source data on its lonesome on a sheet?
I ask because deleting rows of a table and moving cells up below might disrupt other data on the sheet. If the entire row of the sheet can be deleted, that makes things easier still.

[pcarmour]

Hi p45cal,
Thank you for looking at this. The source data isn't in a table so deleting doesn't cause any problem. Yes the entire row can be deleted.

[pcarmour]

Hi p45cal,
Thank you for looking at this. The source data isn't in a table so deleting doesn't cause any problem. Yes the entire row, or multiple rows if more than one was selected, can be deleted.

[p45cal]

Sorry, I should have asked before; how are you populating the listbox?

[pcarmour]

Sorry, I should have asked before; how are you populating the listbox?

Hi,
I am populating the listbox from the userform rowsource which collects the data from range A4:O200 on the source spread sheet.

[p45cal]

try something along these lines:
Private Sub CommandButton1_Click()
RowSourceAddress = ListBox1.RowSource
If Left(RowSourceAddress, 1) = "=" Then RowSourceAddress = Right(RowSourceAddress, Len(RowSourceAddress) - 1)
Set RowSourceRange = Range(RowSourceAddress)
RowSourceRange.Rows(ListBox1.ListIndex + 1).Delete
ListBox1.RowSource = "" 'seems to ensure no row is selected in the list box after deleting
ListBox1.RowSource = RowSourceRange.Address(External:=True) 'update RowSource
End Sub
Which only deletes the extent of the RowSource row, not the entire row of the spreadsheet.

I think the code could be simpler, but it depends on how do you set the RowSource for the list box?
And while this works for single select listbox, it won't for a multiselect one.

[pcarmour]

try something along these lines:
Private Sub CommandButton1_Click()
RowSourceAddress = ListBox1.RowSource
If Left(RowSourceAddress, 1) = "=" Then RowSourceAddress = Right(RowSourceAddress, Len(RowSourceAddress) - 1)
Set RowSourceRange = Range(RowSourceAddress)
RowSourceRange.Rows(ListBox1.ListIndex + 1).Delete
ListBox1.RowSource = "" 'seems to ensure no row is selected in the list box after deleting
ListBox1.RowSource = RowSourceRange.Address(External:=True) 'update RowSource
End Sub
Which only deletes the extent of the RowSource row, not the entire row of the spreadsheet.

I think the code could be simpler, but it depends on how do you set the RowSource for the list box?
And while this works for single select listbox, it won't for a multiselect one.

Hi p45cal (http://www.vbaexpress.com/forum/member.php?u=3494)
That is really good, yes it's deleting a single selection from the source sheet, exactly what I want. Can you please try to find a way of deleting the multiple selections, or is there a way to repeat your code?

Thank you very much for your expert help.

[p45cal]

OK, which of the three choices have you gone for, for the listbox's MultiSelect property?
And again, how (and when and where) do you set the RowSource for the list box?

[pcarmour]

OK, which of the three choices have you gone for, for the listbox's MultiSelect property?
And again, how (and when and where) do you set the RowSource for the list box?

Hi, Thanks again, I have selected 2- fmMultiSelectExtended in the listbox's MultiSelect property. I have only selected the rowsource in the ListBox user form property, I haven't added any rowsourse in the code.

[p45cal]

try, adjusted for your userform of course:Private Sub CommandButton1_Click()
Dim RowsToBeDeleted As Range
RowSourceAddress = ListBox1.RowSource
If Left(RowSourceAddress, 1) = "=" Then RowSourceAddress = Right(RowSourceAddress, Len(RowSourceAddress) - 1)
Set RowSourceRange = Range(RowSourceAddress)
For i = 0 To ListBox1.ListCount - 1
If ListBox1.Selected(i) Then
Set RowsToBeDeleted = Union(RowSourceRange.Rows(i + 1), IIf(RowsToBeDeleted Is Nothing, RowSourceRange.Rows(i + 1), RowsToBeDeleted))
End If
Next i
If Not RowsToBeDeleted Is Nothing Then RowsToBeDeleted.Delete
ListBox1.RowSource = ""
ListBox1.RowSource = RowSourceRange.Address(External:=True)
End Sub

[pcarmour]

Fantastic, That is it, Genius!!
Single or multi rows deleted as required, Brilliant bit of code.
Thank you so very much.