Empty rows appearing after populating a listbox object (VBA)

weltretter

New Member
Joined
May 3, 2022
Messages
2
Office Version
  1. 365
Platform
  1. Windows
Hi everybody,

hope you're all doing great.

I got stuck at my current project and after searching the internet for hours and even days, I have to ask for help on my own?
I've first heard of VBA like 4-5 months ago. Now I'm trying to support my daily business at work and make it easier to handle certain processes.
Due to that my codes are mostly like a puzzle with pieces from different forums all over the web, so please don't be to harsh on me, because I'm pretty sure I'm not using the most efficient and logic ways to solve my problems. But for my requirements it's working (well, until now) ?

So, what's the matter?
While I am populating a listbox from a table there will be multiple empty rows added to the list (on a userform, using VBA).
As you can see in the following screenshot (notice the scrollbar ?)
1651571850559.png

Listbox populated with empty rows


How's my setup?
-I've got multiple tables in a worksheet
-Every table got renamed to make it easier to find
-You have to enter a number in a textbox and press a button to start the process
-The number accords to one specific table
-Listbox got populated with some data from the columns of this specific table

1651572254016.png

This is one of the tables the listbox is using as source


VBA Code:
[/SIZE]
TableName = "Tabelle" + TextBox1.Value

Set Table = ActiveWorkbook.Sheets("Tabelle1").ListObjects(TableName)

TableRows = Table.Range.Rows.Count

TextBox2.Value = Table.DataBodyRange.Cells(1, Table.ListColumns("Name").Index)

For i = 0 To TableRows

    With ListBox1
        .ColumnCount = 4
        .ColumnWidths = "80;80;70;20"
        .AddItem
        .List(i, 0) = Table.DataBodyRange.Cells(i, Table.ListColumns("TZL").Index)
        .AddItem
        .List(i, 1) = Table.DataBodyRange.Cells(i, Table.ListColumns("Mtr").Index)
        .AddItem
        .List(i, 2) = Table.DataBodyRange.Cells(i, Table.ListColumns("Plc").Index)
        .AddItem
        .List(i, 3) = Table.DataBodyRange.Cells(i, Table.ListColumns("Verfügbar").Index)
    End With

Next
[SIZE=3]
The piece of code which is used to populate the listbox


Can somebody please tell me where those empty rows (in this case there are 25 empty rows) came from?


Best regards from Germany
 

Excel Facts

Workdays for a market open Mon, Wed, Friday?
Yes! Use "0101011" for the weekend argument in NETWORKDAYS.INTL or WORKDAY.INTL. The 7 digits start on Monday. 1 means it is a weekend.
You have too many AddItem lines in there. You should just have one for each row of data, not one for each column.
 
Upvote 0
Solution
It would be quicker not to loop though and just use:

Code:
ListBox1.List = Table.Liscolumns("TZL").Databodyrange.Resize(, 4)

since the columns are next to each other.
 
Upvote 0
You have too many AddItem lines in there. You should just have one for each row of data, not one for each column.
Damnit :p Works great now - thanks a lot for your quick help!

Also thanks for your hint trying it without looping - I'll give it a go.

Have a great day!
 
Upvote 0

Forum statistics

Threads
1,213,527
Messages
6,114,140
Members
448,551
Latest member
Sienna de Souza

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top