How to make a macro that sorts all values between two other values?

[pibbz]

Hello, im a new guy here, but i've been using excel for quite a while. However, i have stumbled upon a new problem, and i hope that you guys can help me

My spreadsheet is used to log recieved applications from customers.
Coloumn A contains the values i want to sort: ACCEPTED or DECLINED.
The problem is, that this coloumn also contains the name of the months.
Short summary, A1:A1500 contains either ACCEPTED, DECLINED, JANUARY, FEBRUARY and so on.

What i want to do, is to make a macro that sorts the applications like this:

Please Login or Register  to view this content.

Aka, the accepted applications are listed on top, then the declined (easy, as you can use Ascending/descending based on your needs), but the problem is when it comes to the next month. I need it to stop there, and since i have no idea how many applications we might recieve in the upcoming months, this stop point has to be dynamic.

What i've done so far, is to make a "check" field in the X,Y and Z coloumn, that checks if the cell in A1 = ACCEPTED, it returns a value of 1, if its DECLINED, it returns 2, and if its any month based on a vlookup table, it returns 4.

So, main question is, how can i sort all the 1's and 2's between the 4's? And make the macro stop sorting when it reaches a 4?

Hope i explained myself decently, and dont be afraid to ask for more info if thats what you need. Sorry for any potential spelling errors aswell. Looking forward to an answer or two

[flex]

Try this example I have added two columns, one similar to the one you described. The other contains the following formula to allow you to sort your data:

=SUMIF($A$1:A1,">4")+IF(A1<4,A1,0)

[pibbz]

Try this example I have added two columns, one similar to the one you described. The other contains the following formula to allow you to sort your data:

Not quite what i needed, as far as i know. I've uploaded a small example.
What i want, is a macro that sorts the data from 1's to 2's, and stops when it reaches a "4", then starts over again AFTER the "4", without knowing which cell it might be in.

This is because i never know how many rows a "month" might contain.

[flex]

I have included my formula into your workbook, you simply need to select all of the data and then sort by column D, you can hide this columns and set you macro to automatically sort range(a3:h3).xldown by column d if you still want to use your macro button

Range("A3:H3").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Sort Key1:=Range("D3"), Order1:=xlAscending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal

[pibbz]

This is it! Thanks a lot! Hot mama, that code didnt make sense, but it worked

Since im retarded, i have to understand what the string does, so ill mess around with it until i do. Thanks a bunch! :D

[pibbz]

Ok, just one little flaw.

What i need, is to have coloumn D behave just like coloumn C does, when it comes to auto-filling the same formula over and over.

When i enter the information in Col A, Col C auto-fills with this formula:

Please Login or Register  to view this content.

Then, i need Col D to fill with this forumla:

Please Login or Register  to view this content.

IF Col C contains any data.

This is because, if i suddenly have to insert a row between two existing "already sorted" rows, it wont sort because the cell in Col D does not contain any value.

Same goes for adding new rows at the bottom of the sheet (like row number 16 and so on).

Doable?

[flex]

I added an autofill to the macro so that it copies the formula's all the way down columns c and d

[pibbz]

I added an autofill to the macro so that it copies the formula's all the way down columns c and d

The macro works perfectly, with one exception.

The column i use for the sorting (C & D) in the example document, is actually column Y & Z in my main document. When i change the values in the macro to reflect that, the sorting halts, and the macro returns "Selection is too large" and marks the "Selection.FillDown" part.

I reckon this, and the line above, is for the auto-fill part. All i need is for this line to be adjusted to Y & Z instead of C & D, and ill be done

However, i fail at doing this myself. Would you mind taking a look at it, and provide a short explanation on how this line works?

Thanks - have a nice weekend!