Problem with empty rows appearing in listbox

[Julesdude]

Hi, I'm grateful for help I've received with the following code to populate a listbox in a userform with rows from my worksheet that match a certain criteria, namely that there is a value in column G and that there is a value greater than 0 in column J for that row.

The problem I have is that in the defined range, there are some rows that DON'T match this criteria. They are correctly ignored and don't appear in the listbox, however, after the rows that do appear I have loads of blank empty lines in the listbox that I can click on. This makes the scroll bar very sensitive.

I'd like to get rid of these empty rows in the listbox so that only the filtered lines matching the criteria can be clicked on and the list ends after that.

I was also hoping to get fixed column headers for the listbox, but I'm not sure I can do that?

Can anyone help? It's probably a simple thing I've overlooked.

The code I have so far is

Please Login or Register  to view this content.

[Julesdude]

Hi, found the answer so prob solved. However, still looking for ways of getting column headers to stay up.

Please Login or Register  to view this content.

[snb]

Although your code can be improved.....

You need no more code than this:

Please Login or Register  to view this content.

You have to put the _Initialize VBA-code into the userform-module.

[Julesdude]

Thanks snb, that's nice code, although a bit too simplified for what I'm looking for it to do because I'm not looking to include every row of the sheet in the listbox, only those that match certain criteria.

Well, I ran into a further problem!!! When I click the submit button to my userform, the new values that the user has entered into a couple of textboxes don't now 'find' the row selected in the listbox by the user and matching that row with that on the sheet to edit it. I can't see what's going wrong here.

Here's what I have so far.

Code for sorting listbox in userform:

Please Login or Register  to view this content.

Then, when the user selects a line from the textbox and enters the values, and clicks a command button, those values should then be entered into the worksheet with the following code which is NOT WORKING:

Please Login or Register  to view this content.

[snb]

I wasn't able to open your example file .....

[Julesdude]

Ah I didn't upload one. Would it help? Happy to do it but would be time consuming. Was hoping I wouldn't need to. The rw variable seems to be correct - it is constructed by bolting on the info in each column for the row selected in the listbox. It is separated by ',' in the string and the application.join function matches this with each row in the worksheet to find the corresponding row there.

But for some reason it is not matching this variable with the row in the spreadsheet.

[Julesdude]

Can anyone help please?