Transfer Listbox Rowsource to Worksheet Range

[LOSS1574]

How do I Transfer a Listbox Rowsource to Worksheet Range?

Let's say I have a userform with 1 list box and an up & down button. I adjust the data in Listbox 1 using the up/down button. I want to save the data back to the worksheet.

Also, the listbox is a multicolumn and has four (4) columns of data.

[nst1107]

Here's an example how to do that. Download this file and run the userform.

[LOSS1574]

How do i modify the code to add 2 listboxes of data to the same active worksheet range..

Step 1) Add data from listbox 1 to the active worksheet
Step 2) Add data from listbox 2 to last blank row (end of the data added from listbox 1) in the active worksheet
Step 3) clear both listboxes

Thank you...

[nst1107]

This is how:

[LOSS1574]

Once again thanks for your help and assistance.

I'm having trouble with my code (see below). When the sheet has pre-exisiting data in the worksheet. The data from both listboxes is being entered into the second row in lieu of the first row. I've tried to clear the ranges on the sheet and then place the data to no avail. Please advise
[vba]Sheet1.Activate
With Sheet1
Range("A1:F5000").Clear
End With
Dim i As Integer, j As Integer
With Sheet1
For i = 0 To ListBox2.ListCount - 1
For j = 0 To ListBox2.ColumnCount - 1
.Cells(i + 1, j + 1) = ListBox2.List(i, j)
Next
Next
For i = 0 To ListBox3.ListCount - 1
For j = 0 To ListBox3.ColumnCount - 1
.Cells(i + ListBox2.ListCount, j + 1) = ListBox3.List(i, j)
Next
Next
End With
ListBox2.Clear
ListBox3.Clear
[/vba]
Kind Regards

[nst1107]

Not sure what the problem is without looking at your workbook. The code you've posted looks good to me, and it runs fine when I test it. Is the first item in your listboxes a blank row?

[Kenneth Hobs]

As Nate said, it looks ok. UsedRange might be a better choice.

Another method:
[VBA]Private Sub CommandButton1_Click()
Worksheets("Sheet1").UsedRange.Clear
AddArrayToColAOnSheet ListBox1.List, "Sheet1"
AddArrayToColAOnSheet ListBox2.List, "Sheet1"
End Sub

Private Sub AddArrayToColAOnSheet(anArray, sheetName As String)
Dim r As Range
With Worksheets(sheetName)
Set r = .Range("A" & Rows.Count).End(xlUp).Offset(1)
If IsEmpty(.Range("A1")) Then Set r = .Range("A1")
r.Resize(UBound(anArray, 2), UBound(anArray, 1)).Value = anArray
End With
End Sub[/VBA]

[LOSS1574]

I've tried a couple different ways and it seems the data is not finding the first blank row, instead it's replacing the last row of data..

Should i use a find last blank row of data piece of code?

I'll post a sample file in a few.

[LOSS1574]

Please see the attached sample file..

The problem lies in Row 22 on sheet 1.

The row should read: 22, test22, env22, cas22, und22 however, the row has been deleted or something worse.

I'll look into your suggestion from the above post.

[nst1107]

Here's the fix:[vba] .Cells(i + ListBox2.ListCount + 1, j + 1) = ListBox3.List(i, j)
[/vba]
Edit: Interestingly enough, if you fill the listboxes using the .List() method, as in my example, you do not want the + 1. But, if you use the .RowSource property to fill the listboxes, you need the + 1. I wonder why this is.