Sort listbox by number

Hi

I have to sort the Rows in a listbox, on an userform, by number in a columnA.



Do any one have a solution for this?

Regards

Tony

There are ways to do it but it would depend on the data and how the listbox was filled.
1. Listbox has 1 column or more?
2. Where does data come from that fills the listbox?
3. How was data added to listbox?
4. Are there no duplicates and same number of rows in Column A as there are rows in the listbox?

An attached short simple file would help us help you more easily and better. Click Go Advanced button in lower right of a reply and then the Manage Attachments hyperlink below the reply box to attach files.

Hi Kenneth.

Listbox have 15 columns.

Data is comming from af table in a sheet.

The listbox is used to add new Rows in the table.

The column A is an ID row so no dupliclates.

I want the listbox to show the highest ID in the top, but in the table the highest ID should be in the bottom.

Right now highest ID is in the bottom both plages, so I want to sort the listbox, before showing.

Upload finish

In a macro, I would make a Scratch WorkSheet and copy your data for the listbox. You can then sort it and use that as the RowSource.

Rather than using CountA to get the last row in your dataset, I would use a column that must have data. That is usually the first column. e.g.

Please Login or Register  to view this content.

Thank you for your time.

I Can see your idea, to make extra a sheet,but is there no way to control the sort, direct before loading the boxlist? To avoid an extra sheet.

As I see it, if I change the irow, it Will also change my final sheet (Ccp3) upside Down, I am trying to avoid this.

irow is simply the row number. CountA() counted an extra row because you had a smiley face character in that column. The 2nd commented line in #5 is the way that I would normally do that. In this case, it would be even worse as there would be several blank rows from end of data matrix to the row with that character. So, I used the down method. That has its own pitfalls. e.g. A blank row in dataset because of a blank value.

A scratch worksheet could also be placed into a new scratch workbook. I usually prefer that method and then just close it without saving. e.g

Please Login or Register  to view this content.

It is a simple matter to sort that by viewing a recorded sort.

RowSource shows the title/row's column headings which is about the only reason I use that sometimes.

I normally like to use LIST in a Listbox to fill it. It gives me more power to add or remove list rows. Of course we trade-off the column header row feature of RowSource.

Like the List method, a ArrayList would also need that column heading row. One way I handled that in the past was to use two listbox controls with a small one row listbox to hold the column headings. ArrayList methods can be sorted so that might be much faster than the scratch method.

So, 2 array methods that I thought of can be sorted. One can get into weird sorting issues with numbers treated as strings with those methods and the standard range sort.

Here is an example of the ArrayList sorting method.

Please Login or Register  to view this content.

Hi Kenneth

Thank you I Will try with the temp worksheet.

That is a great help. Thank you

Hi Kenneth I have tryed to use your code, but it gives me failure.

Not enough memory, if I try to use other funtions like EDIT

Do you know a way out of this ?

How many rows of data are there?

Try a manual copy of table to another blank workbook.

DoEvents after the copy line might help.

There are 4 or 5 rows

You must have something else going on. I used your example file to test.