Excel help - sorting using a macro?

My Excel 'skills' have hit a block wall - It sounds like an easy thing to do but its well beyond me!
All i want to do is 'sort' 3x cells
i.e. column A has 3 numbers below it ( in 3 cells) but i need them sorting high to low, the numbers may be 3.14, 3.45, 3.37 for example.
I realise i can just use the 'sort' function (took me half an hour to work that out:P) but i have 100's of these 3 cell combinations - also i need to be able to refill the cells with new numbers and the 'macro'? sorts it straight away? excuse my ignorance if i'm not making sense regarding macros or anything Excel! :)
Tips/help would be appreciated - i'm using excel 2010
Cheers Dean

geek_dino7, Jul 7, 7:17 pm

Sorting "straight away" indicates you want to use an On Change type statement in your macro.

It may be easier (if you don't have heaps of Excel VBA knowledge) to simply write a macro that works on a hot key combo, or assign the macro to a button.

In any event - I used the macro recorder, and relative references in my code to get some code that will do what you want. You have to select the first cell of your 3 cell column, then execute the macro.

Code can be found here with an example 1 page workbook. I assigned the macro to a button when I tested it, however I removed the button when I uploaded to dropbox - but you should be able to figure out how to use this. Alt-F11 to show the macro code.

https://www.dropbox.com/s/3wnwgmjtv7ujrgm/example%20sort.xlsm?dl=0

geek_clip1, Jul 7, 7:35 pm

gahh - dropbox is doing funny things with excel files and wants to open it Excel online.

Try this link to actually download the file

https://dl.dropboxusercontent.com/u/33439384/example%20sort.xlsm

geek_clip1, Jul 7, 7:39 pm

Hey Andrew my Excel skills are very basic - i don't even know what excel VBA is without googling - just clicked on your link but i get 'link not found'
Awesome that you have spent some time looking into this thanks:) hopefully you could try and put it on dropbox again? :)
Cheers Dean

geek_dino7, Jul 7, 7:46 pm

https://dl.dropboxusercontent.com/u/33439384/example.xlsm

Ctrl-q keys together to execute macro

geek_clip1, Jul 7, 7:52 pm

Excellent thanks - i have the sheet and enabled macros on it but unsure hpw it works? if i change the bottom number do i have to click something to enable the macro to 'sort' them?

geek_dino7, Jul 7, 7:58 pm

ahhhhhh you are ahead of m thanks 0 will try that - told you i'm basic! :) !

geek_dino7, Jul 7, 7:58 pm

Wow! that is awesome thank you so much for spending the time to help a stranger - i'll make sure i do the same :)

geek_dino7, Jul 7, 8:04 pm

Take a look at the code - Alt-F11 to open the code windows (known as the project area) - then click open the box marked Modules (on the upper left) - and open Module1 - that will show you the code that runs when you execute the macro.

The code can be stripped right back to only the necessary bits required to sort - but as I used the macro recorder to generate the code - and couldn't be bothered editing the code down - it looks a little bloated.

Have a look online for simple macro or Excel VBA tutorials to get a better understanding of whats going on with macros, and automating Excel.

geek_clip1, Jul 7, 8:07 pm

Oh yes i just looked at the code - not sure i'll be able to pick that up though - i don't use excel that much so forget things way too easily! . I'll have enough trouble getting that macro put on each cell but i can google that tomorrow :) Thanks again

geek_dino7, Jul 7, 8:14 pm

you can cheat - and just reuse the workbook.

Delete all the numbers in this workbook - add as many worksheets as you want - then copy and paste your worksheets into it.

The macro will stay - so long as you keep saving your workbook as a Excel Macro Enabled workbook (in the save options)

geek_clip1, Jul 7, 8:16 pm

that sounds like a bit of me! . will do that cheers

geek_dino7, Jul 7, 8:23 pm

you can get really tricky with macros and write code that sorts 3 numbers highest to lowest in a column - no matter what cell you click then start the macro - say you change the last number in the column - and can't be bothered clicking the first cell at the top of column, before starting the macro - so long as there is a blank space below the last number in the column, a macro could figure out the position of the cell you changed, and sort the column accordingly

This example workbook doesn't do that - but its not too tricky to adjust the code to make it a little more usable

geek_clip1, Jun 12, 8:54 pm

Share this thread