Export ListBox Data to Sheet range

Question

In Excel VBA Userform, I'm working. A ListBox is on a Userform I have. Seven columns make up that listbox, and new entries are added to make the rows complete. That element is operating perfectly.

The information will then be sent to a particular set of cells in a sheet using a command button I have. I own the next code:

Private Sub CommandButton1_Click()

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

End Sub

The issue is that all the rows in the cell range are filled with the same item if there is only one row in the listbox. The remaining cells display the following if I have two distinct rows, each with two different items: #N/U.

Is there a VBA code I can apply so that it only exports the listbox rows that have content and hides "#N/U" in the empty cells?

Answer 1

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