Excel Select Sort only Values Please

[bont]

Hi all,

I am able to do a select sort by editing my macros, but I have one problem. The problem is that when I sort, the colomn formatting is also taken with the values. Is there any way I can set the select sort to only sort the values?

 

[VBAjedi]

As far as I know, there is no way to sort data without cell formatting following it around (normally that's a GOOD thing!).

However, you should be able to get what you want with the following approach.

1) In your code, before your sort routine, copy the entire range that contains the data you are about to sort
2) Do your sort routine
3) Paste special > Formats to restore the formats to their pre-sorted state (use the macro recorder to see the code for this). If you encounter trouble with this step when running your code, Excel may be clearing the clipboard when it sorts (it does wierd stuff like that sometimes). If that's the case, just paste the entire range to another (hidden?) worksheet before you sort it, then copy and paste the formats back from that hidden sheet after you sort.

Let me know if this approach works for you!


VBAjedi

 

[bont]

Thanks. I was going to do something similar, but your technique is so simple, I have to use it.

If anyone has any algorithms for sorting excel on this type of level, please notify me. I would most interested. I had contiplated using an array for my purpose, but didn't really want to spend a lot of time on it.

Thanks again.

 

[VBAjedi]

Surprisingly, VBA doesn't have the built-in sorting ability for arrays that Excel has for ranges. So sorting arrays in VB is a bit awkward and messy.

This is one of those areas where it's better to take advantage of the pre-made functionality of Excel! You could copy your values to a hidden range, sort them there, and paste the sorted values back to your visible range, but that seems like it's just the other way around the bush to the approach I already suggested.

VBAjedi