Moving multi-column Listbox back to Excel

Question

I've populated a Userform from four columns, following the advice in VBA Excel Populate ListBox with multiple columns.

I now want to allow my users to be able to remove any line, and for the contents in the new Listbox to be moved back to the Excel worksheet, using the same columns.

EDIT: The full list of items can be anything from one single line to dozens of lines. As an example of how my code is working: assuming, from a list of 5 lines, Line number 3 is removed by the user. In this case, all entries up-to the deleted point are restored to the correct range (so lines 1 and 2 are correct). Lines 4 and below are not restored.

Is there a way to get all remaining lines to populate in Excel?

Users can delete whole lines from this code:

Private Sub Remove_Click()
Dim i as Integer
For i = Me.ListBox1.ListCount - 1 To 0 Step -1
        If Me.ListBox1.Selected(i) = True Then
            Me.ListBox1.RemoveItem i
            Exit For
        End If
    Next
End Sub

The code I'm using to try to re-populate the Excel sheet is here. No error messages are shown. It's as though the code thinks it's finished.

Private Sub CommandButton1_Click()
Dim ws      As Worksheet
Dim rng     As Range
Dim MyArray
Dim x As Long
Set ws = Sheets("EnteredData")

Set rng = ws.Range("M3:P" & ws.Range("M" & ws.Rows.Count).End(xlUp).Row)

    rng.Clear

    MyArray = Me.ListBox1.List
    x = Me.ListBox1.ListCount - 1
    ws.Range("M3:P" & x).Value = MyArray

End Sub

I'd really appreciate any help in resolving this issue. Thanks

Answer 1

It looks like the problem lies with this line...

ws.Range("M3:P" & x).Value = MyArray

You'll need to account for the first three lines, so it should be...

ws.Range("M3:P" & 3 + x).Value = MyArray