Macro To Sort Data in a cell range row by row

Hello To All. I am an older aged newbie to both Excel and Computing in general. I would be extremely grateful for any help in solving my problem as I have spent months getting myself to this stage and now at the very end, I am basically sorting the data manually, which defeats the purpose of my efforts completely. Please forgive my description of my requirements, as I may express them in a non specific manner not being used to the correct Excel terminology.

The Macro will always start in cell C9, in my example attached there are twelve columns containing data that should be sorted row by row

There is a sorting pattern that will always be static - Skip, Sort, Sort, Skip, Sort, Sort and so on down the rows

C9 skip - this row range will always be left unchanged

C10:N10 sort this 12 cell range - the 13th cell O10 contains the text RE letting the macro know it has reached the end of the row and move to next row

C11:N11 sort this 12 cell range - the 13th cell O11 contains the text RE letting the macro know it has reached the end of the row and move to next row

C12 skip - this row range will always be left unchanged

C13:N13 sort this 12 cell range - the 13th cell O13 contains the text RE letting the macro know it has reached the end of the row and move to next row

C14:N14 sort this 12 cell range - the 13th cell O13 contains the text RE letting the macro know it has reached the end of the row and move to next row

Everyday the number of rows changes as do the number of cells in the row range to be sorted

But the starting cell will always be C9 and the pattern will always be Skip Sort Sort Skip Sort Sort all the way down

So if there were 441 rows C9:C449

C9 SKIP
C10 SORT
C11 SORT

C12:C446 SKIP SORT SORT

C447 SKIP
C448 SORT
C449 SORT

When the last row range has been sorted the word END will always be in the next C cell in the next row, so the macro should finish when it gets to the C cell containg END, with 441 active rows it would find END in cell C450

With 30 active rows C9:C38 it would find the word END in cell C39 and should stop

Likewise with the row range across to be sorted, if there were only 4 cells with data to be sorted starting with C9:F9 the text RE would always be in the G column all the way down G9, G10, G11 and so on down the relevant rows

The data in all cells that are to be sorted is imported from a different workbook so all cells contain a FORMULA linking to this outside workbook

So in the example attached where you see BLANK this means that there is no visible data in the cell but there is a FORMULA

All cells to be sorted are formatted as numbers to 2 decimal places

the data should be rearranged high to low from left to right

the attached is merely an example and not an actual worksheet but covers what the data looks like pre macro and exactly how it should look after running the macro

In actuality I could have 40+ worksheets daily with 600+ rows in each to be sorted

Again I apologise as I know I may not have expressed my requirements in a clear and succinct manner.

Thanks In Advance for any help forthcoming.

See
https://msdn.microsoft.com/en-us/lib...ffice.11).aspx

jindon, thank you ever so much for replying and taking the time to help me with a solution to my problem. I greatly appreciate it, again many thanks.

As I feared and said above, I probably did not make my requirements very clear and concise up above. I apologise for this and would be grateful if you would continue to help me.

The file I provided above was only a sample of how data appeared before any Macro and how the sample data should appear after a Macro was inserted and run. I only provided a small number of rows and columns as a sample.

The Macro should not contain a specific number of rows or columns within it but should rely on finding RE (which will always be at the end of each row) to tell it that it has finished sorting that particular row range and should move down to the next row

The Macro will always find END in the C cell directly after it has sorted it's last row letting it know to stop running.

So the same Macro should be able to run in any Worksheet regardless of the number of rows or columns to be sorted. I can have 40+ worksheets daily all with different numbers of rows and columns.

I have attached a Workbook with 3 Worksheets each containing different numbers of rows and columns, as before C9 is the starting point and Skip, Sort, Sort is the pattern down the rows.

I would prefer to have NO blue box appear but just code that I can record into my Master Worksheet which I copy to produce individual worksheets and run it from the Macro section of each worksheet.

Thanks again for taking the time to help me.

Try change to

Please Login or Register  to view this content.

Sorry jinjon, but I don't think you are understanding me.

Macro should work in all 3 worksheets in my new attachment Sort Data.xlsm in my post #5. Please read again

Do not put Message box in code.

Do not put number of rows in code.

Do not put number of columns in code


Code should be the same for worksheet 1, worksheet 2 and worksheet 3 in Sort Data.xlsm attached in post 5 above different to original example attached

Is it not working with your attached workbook or what?
I'm testing here and confirmed that it is working.

Do you have more sheet(s) that should not be sorted?

Sorry jinjon,

missing text in my post #7

It seems to be having a problem with values beginning with 100

I run code on worksheet 1 and problem on row 101 cell G101 has 100.02 in it should be in cell C101

code in my worksheet puts beginning with 2 digit values first then 100 then 0 then blanks

so i get something like 33.02 26.01 100.03 0.04 blank instead of 100 being first

and only partially running on other 2 worksheets see attached with code included and ran

row 10 in attached above worksheet 3 putting single digit ahead of double digit

thanks jinjon.

its going as follows beginning with

1 digit

2 digit

3 digit

zero

blank

Instead of

3 digit

2 digit

1 digit

zero

blank

seems to be Excel, does the same with a different worksheet completely using built in custom sort function on a row of similar data

Ahhh, I see they are all Text, not numeric...
Try change to

Please Login or Register  to view this content.

jinjon, thank you ever so much for your help and providing me with a solution. It is greatly appreciated, thanks again. Mike.

You are welcome and thanks for the rep.
I should be more careful at the test....

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.

jinjon just a couple of final things, if you could please.

Could you alter the code (your post #12) to only run in the Active Worksheet and not all Worksheets in the Workbook at the same time.

Also it is arranging the cells as follows:

Blank

3 Digit

2 Digit

1 Digit

Zero

Could you make it put Blanks last

3 Digit

2 Digit

1 Digit

Zero

Blank

Thanks, Mike.

It should move blanks to the end.

Here's the 2 lines from Sheet3 that have 12 columns after sort.

Please Login or Register  to view this content.

Please Login or Register  to view this content.

Thanks jinjon, I test it shortly and get back to you.

Mike,

I have no idea why it sorts like that.

This is the work around that I can think of.

Please Login or Register  to view this content.

jindon,

That does it.

I cannot thank you enough for your help, to say I greatly appreciate it, is an understatement.

Thank you ever so much.

Mike.

Thanks also to FDibbins for showing me how to upload to begin with.

You are welcome and thanks for the reps.