sorting values in a range

[danane]

I want to sort a range. The problem is that the cells in column M have formulas like =MIN(H3:K3). I only want to sort the values, not the formulas.

I made a code, but the XLVALUES or something like that is missing. I don't know where to put this in ... Thanks for helping.

Code:

Range("A3:M10").sort Key1:=Range("A3"), Order1:=xlAscending, Key2:=Range( "B3"), Order2:=xlAscending, Key3:=Range("M3"), Order3:=xlAscending,
Header:=xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, DataOption1:=xlSortNormal, DataOption2:=xlSortNormal, DataOption3:=xlSortNormal

 

[xlbo]

AFAIK, there is no sort option to sort just values as opposed to formulae

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[danane]

Really? Is there no other way to do that?

 

[PHV]

what happens if you do the sort manually ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[danane]

No, it should happen automatically. It's a business application...

Any other ideas?

 

[PHV]

I knew it should happen automatically, as you asked in the VBA forum.
I just asked you if a manual sort does the trick you wanted ...

 

[danane]

I recorded the macro, so I did it manually.... I don't how I can do it else.

 

[xlbo]

How do you want the sort to happen ?? just ignore the formulae?? what if some data below the formula would sit above it after the sort ??? how on earth would excel know what to do.

Only way I can see you being able to achieve this is to write a VBA routine to scoop all the data into an array, along woith a flag for whether it is a formula or not and bubblesort it within the array before outputting to the sheet again - it could get very messy I'm afraid...

Questions is WHY do you need to do this and WHY must the formulae be where they are ??

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[AnotherHiggins]

Do you still need the formulae after the sort?

How about replacing each formula with the value it returns and then sorting?

To do that, you would just add
range("A:B").value = range("A:B").value

[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ181-2886 before posting.

 

[danane]

I don't want to ignore the formulae.
Example: Cell M3 has the formula =MIN(H3:K3) and the VALUE 2, because:

H3, Value 3
I3, Value 2
J3, Value 9
K3, Value 4

Alright?

So when I want to sort column m, i want to sort the values, but Excel just sees the formulae...

 

[xlbo]

ok - I'm a little bit confused now. Pls can you answer the following so I can get a better picture,

You have just formulae in column M?

You have formulae & hardcoded values in column M?

When you sort column M, it is treating the formula results differently to the hardcoded values?

When you sort column M, you don't want the position of the formulae to change - just those of the hardcoded values?

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[danane]

1. yes, just in column m
2. just formulae

You see? The cells show just the values, but behind there are formulae (in column m).

 

[AnotherHiggins]

I think we have hit a language barrier.

Please post a small example of your data as it now, and as you would want it after a sort. Just a few columns and a few rows to let us know what you want.

[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ181-2886 before posting.

 

[xlbo]

Are the formulae returning results or showing as the formula ?? ie in M3, does it display 2 or =MIN(H3:K3)

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[danane]

Let's make it more simple.

I only want to sort column M.

BEFORE:

M1: Value= 2 Formulae= MIN(H1:K1)
M2: Value= 4 Formulae= MIN(H2:K2)
M3: Value= 1 Formulae= MIN(H3:K3)

AFTER:

M1: Value= 1 Formulae= NO FORUMULA ANY MORE
M2: Value= 2 Formulae= NO FORUMULA ANY MORE
M3: Value= 4 Formulae= NO FORUMULA ANY MORE

Hope this helps.

 

[danane]

@xlbo. it displays the result 2.

 

[xlbo]

I think I see - you just want to remove the formulae and then sort ??

With Range("M3:M100")[COLOR=green]'amend 100 to suit[/color]
    .copy
    .paste special paste:=xlpastevalues
    .sort Key1:=Range("M3"), Order1:=xlAscending,
Header:=xlGuess
end with

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[AnotherHiggins]

danane,

As I posted earlier, if you want to replace the formulae with the results, then you can just add this to your code before the sort:

range("M:M").value = range("M:M").value

(This does the same thing as xlbo's code, but in a single step.)

[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ181-2886 before posting.

 

[danane]

xlbo and anotherhiggins thank you very much. your suggested code works fine!!
Sorry for not explainig things exactly from the beginning on...

 

[xlbo]

no worries - got there in the end !!

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question