RANGEs into COLLECTION: deleting them in loop, cause row ranges move to left?

[Gemitec]

Hello

I have a strange Problem: if I loop through a collection, where I put Range into it, and delete them with VBA, the last row somehow "grabs" a range from "right" and pull it to the left.

Please see the pictures:

Procedure_1: here you see the spreadsheet
Procedure_2: This is my Listbox1 on my userform, where I select the value I want to delete (another button starts the sub)
Procedure_3: here you see, I use the common "for each" to loop and delete
Procedure_4: here you see everything is fine, until:
Procedure_5: at the last he wants to delete, he just pull another range from right to left

:O

You know why this is happening?

Here my code for the "delete button" with listbox1 value selected:

Private Sub CommandButton13_Click()
Dim Listbox1_Find                           As Range 'This is the range where it founds the first value in the search
Dim FirstAddress                            As String 'Temporary made to fix the first Found adress of the searched value
Dim Listbox2_Items                          As New Collection ' We place here all the found items
Dim vItem                                   As Range 'This adds the values from the collection to the Listbox
Dim selections As Range

Dim i As Integer
Dim j As Integer

'_______________________________________________________    Start Setting up Userform...

Me.ListBox2.Clear
Me.ListBox3.Clear
Me.ListBox4.Clear

With Einstellungen.Logistik
    On Error Resume Next
    For i = 3 To 10
    .Controls("Textbox" & i).Value = ""
            Next i
    For i = 14 To 22
    .Controls("CommandButton" & i).Enabled = False
    .Controls("CommandButton" & i).BackColor = &HE0E0E0
            Next i
End With

Me.CommandButton15.Enabled = True
Me.CommandButton15.BackColor = &HFFFFFF

Me.CommandButton11.Enabled = True
Me.CommandButton11.BackColor = &HFFFFFF
Me.CommandButton13.Enabled = True
Me.CommandButton13.BackColor = &HFFFFFF

Me.TextBox4.Value = Me.ListBox1.Value
Me.TextBox3.Value = Me.ListBox1.ListIndex + 1
'_______________________________________________________    End Setting up Userform.

   Worksheets("Einstellungen_Versand1").Select
     
        With Worksheets("Einstellungen_Versand1").Range("b1:b" & (Cells(Rows.count, 2).End(xlUp).Row))
          
            Set Listbox1_Find = .Find(what:=Einstellungen.Logistik.TextBox3.Value, LookIn:=xlValues, lookat:=xlWhole)
          
            If Not Listbox1_Find Is Nothing Then
              
                FirstAddress = Listbox1_Find.Address
                On Error Resume Next

                Do
                    Set selections = Worksheets("Einstellungen_Versand1").Range("b" & Listbox1_Find.Row & ": t" & Listbox1_Find.Row)
                    Listbox2_Items.Add selections
                    Set Listbox1_Find = .FindNext(Listbox1_Find)
                  
                Loop While Listbox1_Find.Address <> FirstAddress
                              
                On Error GoTo 0
               
                For Each vItem In Listbox2_Items
                    vItem.Delete
                Next vItem
           
            End If
     End With
End Sub

 

[RoryA]

Specify the Shift argument for the Delete as xlShiftUp. Otherwise you are leaving Excel to guess which way you want to delete.

 

[Gemitec]

Hello Rory
Thanks for your quick and short answer. It works now
Regards