Delete empty row in a Listbox !

[Petter120]

Hello !

I am working whit a Userform that allows me to add data into a specific sheet controlled by a Combobox and then shows the data
on the sheet in a Listbox.

I can add and delete data to the sheets but i have one problem.

If i delete all data in trough the listbox until its empty and i want to add data from scratch, the first row
in the sheet always gets empty and it starts to add data from the second row.

Is there a way to delete the empty rows or get it to start from the first row ?

Best regards

Petter

Please Login or Register  to view this content.

[snb]

Please Login or Register  to view this content.

NB. listbox1.clear is redundant if you use the property .list to fill the listbox.

[Kelshaer]

Try the attached file

To mark your thread SOLVED do the following:
- Go to the first post
- Click edit
- Click Advance
- Just below the word "Title:" you will see a dropdown with the word No prefix.
- Change to Solve
- Click Save

[Petter120]

It still doesnt work quite like i want it. The most important thing is that all data on the sheet shows correct in the listbox whit no blank rows.
Now the listbox leaves the first row out if i add two or more items into the sheet.

It doesnt mather if the data in the sheet has blankrows between it.

Is it possible to make the listbox show it whtout blank rows even if the sheet has blank rows ?

Regards

Petter

[Kelshaer]

Here you go

[Petter120]

Ok it adds the data correct into the sheet but it gets wrong in the listbox in the userform.

This is how i want it to work.

If i type in following in the textboxes

Account Item
11 Apple

In the combobox i chose wich sheet the data goes in to (blad1, blad2 or blad3)
I chose Blad1 from the combobox.

and then press add button.

When i press the add button the data goes in to:

Sheet (Blad1)


Sheet (Blad1) Shows
A B
1 11 Apple
2
3
4
5

Listbox Shows

11 Apple

Until here everything works fine its when i add next account/item there is a problem

Account Item
22 Banans

Combobox
Blad1

Press Add button


Its here it get wrong in the listbox its overwrighting the first input, i want the listbox to show all the data in the sheet so
i can delete the data from the listbox.

Sheet (Blad1) Shows
A B
1 11 Apple
2 22 Bananas
3
4
5

Listbox Shows

22 Banans

Listbox should show

11 Apple
22 Bananas


Hope this makes the issue clearer

Regards

Petter

[Leith Ross]

Hello Petter120,

Your big problem was with the indexing. Ranges are 1 based 2-D arrays. ListBoxes and ComboBoxes are zero based arrays. Their first element is always zero. here is code that I mad changes to. The UserForm will work correctly with these changes.

Please Login or Register  to view this content.

[Petter120]

Still problems maybe im not putting the code in right.
I attach the file again whit your code.

Try to put in 5 items to sheet "Blad1" and you will see that only 4 is shown in
the listbox (the listbox doesnt show the first row )

Then try to delete all of them one by one you will see that everything works fine until you delete the fourth and
the first row suddenly appears and cant be deleted. (its still left in both the sheet and the listbox).


Petter

[Leith Ross]

Hello Petter120,

You need to recall that your first worksheet row starts at row 2, not 1. I assume you meant to do this because row 1 would contain the column headers.

[Petter120]

Yes that sounds right but how do i do that ?

[Leith Ross]

Hello Petter120,

Do you want to start with row 1 on all the worksheets?

[Petter120]

Yes that will be great

[Leith Ross]

Hello Petter120,

Try this workbook and let me know if there are any problems.

[Petter120]

I canīt find the userform in it

[Leith Ross]

Hello Petter120,

I modified this version to display the UserForm when the workbook opens. You can also manually display it by activating the macro named "Run".

[Petter120]

Thank you so much, exactly what i wanted

Best regards

Petter

[Leith Ross]

Hello Petter120,

You're welcome. Glad it is working like you want.