Double sort current selection and delete certain lines

[daniel_c]

Hi everyone!

I have been trying to create a macro that does the following:

1. Sort current selection according to the cell color in row 1 (either white or yellow)
2. Sort current selection according to the values in row 3 (ascending)
3. Delete columns (only in the selection) where the color in row 1 is not yellow (the one used in conditional formatting)
4. Shift cells up

What I have so far is:

Please Login or Register  to view this content.

I am, however, lost on how to sort in row 3 (I have read of the 'offset' function but don't really know how to implement that) as well as on how to delete the columns i don't want...

Any help would be greatly appreciated!

Many thanks in advance

[arlu1201]

What do you mean by sort on row 3? Are your headers in row 3?

Do you have a sample file that you can upload?

To Attach a File:

1. Click on Go Advanced
2. In the frame Attach Files you will see the button Manage Attachments
3. Click the button.
4. A new window will open titled Manage Attachments - Excel Forum.
5. Click the Browse... button to locate your file for uploading.
6. This will open a new window File Upload.
7. Once you have located the file to upload click the Open button. This window will close.
8. You are now back in the Manage Attachments - Excel Forum window.
9. Click the Upload button and wait until the file has uploaded.
10. Close the window and then click Submit.

[daniel_c]

Hi arlu!

Sorry, I mixed columns and rows... (slightly embarassing, I know^^)

So what i meant was, I would like to double sort my selection on:

1. the color of cells in column 1
2. the values in column 3 (ascending)

I attached a sample, as you asked me to.

many thanks for your help!

sample.xlsx

[arlu1201]

This is the sort code -

Please Login or Register  to view this content.

[arlu1201]

I was working on the remaining portion of your code and it was giving me issues with that color that you have chosen.

Is it ok if i put a condition that all rows which are not equal to 1 should be deleted?

[daniel_c]

wow, first of all, THANK YOU! :D

yes, deleting all rows unequal to 1 would be perfect!

It looks like you set fix values for sorting the columns A and C, is that right?
The problem is that I would require the columns to be the first and third columns of my current selection, is that possible?

[arlu1201]

You are welcome.

Are there chances of the selection changing?

[daniel_c]

what do you mean by

Are there chances of the selection changing?

The Columns and the amount of rows included Change, however, I always Need to sort on the first and third column, is that what you meant?

[arlu1201]

You mentioned this -

The problem is that I would require the columns to be the first and third columns of my current selection, is that possible?

By that statement, i understood that sometimes your data might be in columns A to C, sometimes B to D, etc...as you said current selection.