Programmatically sort rows separately based on value in cell?

Hi

It should be straightforward to do using a helper column as follows

1. Use the FIND function to detect each matching row and insert a value in the helper column (e.g 1, x or whatever).

2. Sort the rows on the helper column.

3. Sort the range using the helper column count to determine range size.

4. Delete helper column contents

If you have an example please post and I will add some code for you.

regards

Jeff

Thanks Jeff,

That is a great idea! Unfortunately I don't know that adding a column is something I can feasibly implement. The spreadsheet has 100+ columns and several other sheets in the workbook, with dozens of formulas and lookups across the sheets. Adding a column would be a major undertaking that would have to be reflected in many other places. Additionally, this column was originally intended to be the helper column for another column, so it's starting to get a little too recursive! If it's the only way to solve the problem I'm willing to entertain it but it almost seems easier to continue doing the sort manually.

Is there another way I should think about this? Can I use a match to find all the rows with my ID code and apply a sort, then use a match to find the remaining rows and apply the sort? I'm not as familiar with that so I wouldn't exactly know how to go about it.

Thanks!

Hi

In reality you are not adding a column physically, it can be any blank column and does not reflect at all on anything else in the spreadsheet. The main reason to use it is that all relevant data can be simply brought to the top of the sheet for easier viewing. It is also possible to do it without the column and data would then remain in whatever position it was sorted to alphabetically.

Regards

Jeff

The following uses a bubble sort to move the rows with an "X" to the top of the sheet. It then sorts those rows based on the C column. Lastly, it sorts the remaining rows based on the C column as well.

Please Login or Register  to view this content.

Thank you! I will try this and report back.

Here is a slightly different version. It should run much faster with larger data sets. It only runs through the rows once instead of the number of rows squared - 10 rows of data would force 100 loops of the bubble routine.

Please Login or Register  to view this content.

mdbct,

I am having a bit of trouble getting this to work. My data does not begin until row 19, so I have substituted that for 2 in the row number. I do not get any compile errors. However, when I run the sort all that happens is that rows 2 through 19 are selected (nothing is sorted).

How should I modify this code assuming my data begins at row 19 (that is the first row with data; the header row data is contained in rows 1 through 18)?

Also, if I wanted to sort by a secondary criteria after sorting by row C, is that possible?

Thank you for your continued assistance. I really appreciate it!

Brianala

Try this version. I've highlighted the code section for the secondary sort. In this case, the D column, descending. I also realized I could eliminate one of the looping sections as well.

Please Login or Register  to view this content.