Export ListBox Data to Sheet range-CodePudding

Im working on a Excel VBA Userform. I have a Userform with a ListBox. That ListBox has 7 Columns and the rows are completed by adding new items. That part is working just fine.

Then I have a command button that will send that information to a specific range of cells in a sheet. I have the following code:

Private Sub CommandButton1_Click()

Range("A18:G36").Value = ListBox2.List

End Sub

The problem is that if I only have one row in the listbox, all the rows in the cell range get filled with the same item. If I have two different row with two different item the rest of the cells show this: #N/U.

Is there a VBA code that I can use that only exports the listbox rows that are filled and the empty cells don't show "#N/U".

CodePudding user response：

You have to use ListCount to retrieve the number of rows to insert:

Private Sub CommandButton1_Click()
dim cntEntries as long
cntEntries = ListBox2.ListCount

Range("A18:G18").Resize(cntEntries).value = ListBox2.list

End Sub

CodePudding user response：

ListBox Data to Range

Private Sub CommandButton1_Click()
    
    Dim rg As Range, rCount As Long
    
    With ListBox2
        rCount = .ListCount
        Set rg = Range("A18").Resize(rCount, .ColumnCount) ' reference
        rg.Value = .List ' write
    End With
    
    With rg ' clear below:
        .Resize(.Worksheet.Rows.Count - .Row - rCount   1).Offset(rCount).Clear
    End With

End Sub