Delete Cells based on Extended Listbox selection

[al.extreme]

Hello
I'm using the following code: I found in another post and it works ok.

The code deletes the cell value in column 1 by shifting up, according to the multiple selection in an Exteded Listbox

Since ListBox1 contains 2 columns of data, I instruct the macro to also delete the adjacent cell in column 2

The process repeats according to the number of multiple selections

The only problem is that from time to time, it send me the error message: RunTimeError 2147417848 (80010108) "The object invoked has disconnected from its clients"

Does anybody can help with a different method to delete (from my dinamic range in a workbook) the selected items in an extended listbox (2 columns listbox)?




Worksheets("List_ABC").Select
Dim lastrows As Long
Dim lngIndex As Long
lastrows = Worksheets("List_ABC").Cells(Rows.Count, 1).End(xlUp).Row
Dim strRange As String
With ListBox1
For lngIndex = 0 To .ListCount - 1
If .Selected(lngIndex) Then
strRange = .RowSource
Range(strRange).Cells(.ListIndex + 1, 1).Delete shift:=xlUp
Range(strRange).Cells(.ListIndex + 1, 2).Delete shift:=xlUp
End If
Next
.RowSource = vbNullString
.RowSource = strRange
End With

 

[AlphaFrog]

Try changing this...
For lngIndex = 0 To .ListCount - 1

To this...
For lngIndex = .ListCount - 1 To 0 Step -1

 

[al.extreme]

Hi AlphaFrog

I will try your proposed solution.
I also read in another link an interesting approach, instead of deleting up the columns, clear the range and export the non-selected values from the listbox

Thanks

 

[al.extreme]

Nop, I tried your proposal and I still get the same error at the code line:

Range(strRange).Cells(.ListIndex + 1, 1).Delete shift:=xlUp

I've been wondering if I can offset the range to select column 1 and 2 columns on the same line code, for example:

instead of:
Range(strRange).Cells(.ListIndex + 1, 1).Delete shift:=xlUp
Range(strRange).Cells(.ListIndex + 1, 2).Delete shift:=xlUp


have something like:
Range(strRange).Cells(.ListIndex + 1, 1 & 2).Delete shift:=xlUp

 

[AlphaFrog]

It was a shot in the dark.

I've been wondering if I can offset the range to select column 1 and 2 columns on the same line code

Thy this...

Range(strRange).Cells(.ListIndex + 1, 1)[COLOR="Red"].Resize(, 2)[/COLOR].Delete shift:=xlUp