Export ListBox Data to Sheet range

0 votes

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?

Jan 12 in Others by Kithuzzz
• 28,700 points
67 views

1 answer to this question.

0 votes

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
answered Jan 12 by narikkadan
• 53,160 points

Related Questions In Others

0 votes
1 answer
0 votes
1 answer

How to export data with functions to another workbook in excel

The issue appears to be that once ...READ MORE

answered Oct 30, 2022 in Others by narikkadan
• 53,160 points
71 views
0 votes
0 answers

Data Driven Framework -- how to read and write in excel sheet using Selenium WebDriver with java

I'm using this code to read something, ...READ MORE

Oct 31, 2022 in Others by Kithuzzz
• 28,700 points
116 views
0 votes
1 answer

VBA to protect and unprotect in given range in sheet

It needs a minor tweak to become ...READ MORE

answered Nov 24, 2022 in Others by Kithuzzz
• 28,700 points
192 views
0 votes
1 answer

Retrieve epay.info Balance with VBA and Excel

This code should log you in, provided ...READ MORE

answered Sep 5, 2018 in Blockchain by digger
• 26,740 points
529 views
0 votes
1 answer

How to load file to Excel Power query from SFTP site

Currently, I don't think there is a ...READ MORE

answered Dec 3, 2018 in Power BI by Upasana
• 8,620 points
2,408 views
0 votes
1 answer

Using VBA Excel to create a gramatically correct list

The Excel AND function is a logical ...READ MORE

answered Feb 9, 2022 in Others by gaurav
• 22,940 points
117 views
0 votes
2 answers

How to copy a formula horizontally within a table using Excel VBA?

Hi so basically, create an adjacent column ...READ MORE

answered Feb 16, 2022 in Others by Edureka
• 13,630 points
294 views
0 votes
1 answer

VBA Code to Populate Balance Sheet from Trial Balance Data

It appears that what you need to ...READ MORE

answered Sep 24, 2022 in Others by narikkadan
• 53,160 points
205 views
0 votes
1 answer
webinar REGISTER FOR FREE WEBINAR X
REGISTER NOW
webinar_success Thank you for registering Join Edureka Meetup community for 100+ Free Webinars each month JOIN MEETUP GROUP