Sort macro with descending

[yolanda.silva]

Hi... I'm trying to put in a macro that will sort the data in column
G... what I have now is:

Sub SortMe()
Cells.Sort Key1:=Range("G1"), Header:=xlYes
End Sub


But, this sorts in ascending order and I need it to be descending with
the highest on top? Is there a way to get it to reverse?

Thanks!

(I know I can manually sort but this will be a macro that is set to
auto-run when the file is open so that's why I want it incorporated
into the macro)

 

[Don Guillett]

try recording a macro while you are doing a manual sort to see what
happened. Modify to suit

 

[yolanda.silva]

Probably should've mentioned that I don't know much about macros...
someone else wrote that one for me... I don't know how to record a
macro?

 

[Don Guillett]

If you're new to macros, you may want to read David McRitchie's intro
at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

 

[Gord Dibben]

Sub SortMe()
Cells.Sort Key1:=Range("G1"), Order1:=xlDescending, Header:=xlYes
End Sub

Which you could easily have found by using the macro recorder.


Gord Dibben MS Excel MVP

 

[Pete_UK]

Click on Tools | Macro | Record New Macro and a panel will pop up
allowing you to specify the name of the macro you are about to record,
to choose where it whould be located, and to give the macro a keyboard
shortcut. When this is done you get a small toolbar mid-screen with
only two icons - one to stop the recording and the other to switch
between absolute and relative addressing.

Your actions will be recorded, so Don was suggesting that you should
go through the motions of sorting your data in descending order. At
the end you should click on the Stop recording icon (NOT on the Close
(X) icon). Then you can examine your code - Alt-F11 will bring up the
Visual Basic Editor, and you can amend what has been recorded.

Hope this helps.

Pete

 

[yolanda.silva]

Click on Tools | Macro | Record New Macro and a panel will pop up
allowing you to specify the name of the macro you are about to record,
to choose where it whould be located, and to give the macro a keyboard
shortcut. When this is done you get a small toolbar mid-screen with
only two icons - one to stop the recording and the other to switch
between absolute and relative addressing.

Your actions will be recorded, so Don was suggesting that you should
go through the motions of sorting your data in descending order. At
the end you should click on the Stop recording icon (NOT on the Close
(X) icon). Then you can examine your code - Alt-F11 will bring up the
Visual Basic Editor, and you can amend what has been recorded.

Hope this helps.

Pete





- Show quoted text -

Thanks, Pete You helped me once before!

One other question... is there a way to get it to ignore the blank
rows? I have in my formula that if the sum of them is 0, it just
leaves it blank... but, it's sorting that in so it puts that on top.
I'd rather not have to have the 0's in place... makes the spreadsheet
look... well... crowded

 

[Pete_UK]

If a cell is completely blank then it will get sorted at the end of
your list if it is in increasing order (which is what I normally use),
so presumably it appears at the top in descening order. You might like
to try returning a space " " within your formulae instead of "", as
this should go to the bottom of a descending list.

Hope this helps.

Pete

 

[yolanda.silva]

If a cell is completely blank then it will get sorted at the end of
your list if it is in increasing order (which is what I normally use),
so presumably it appears at the top in descening order. You might like
to try returning a space " " within your formulae instead of "", as
this should go to the bottom of a descending list.

Hope this helps.

Pete






- Show quoted text -

I must be doing something wrong

The table has 7 columns... one for a name, 5 for days of the week
(Monday-Friday) and one for a total at the end... each day, there are
3 total points given - the highest person gets 3, the second highest
gets 2, the third highest gets 1... no one else gets anything. My
formula for the totals is:

=IF(SUM(B2:F2)=0," ",SUM(B2:F2))

I changed it to include the extra space between the "" as you
suggested.

There are 19 rows in the spreadsheet, the first being the header...
but there are only 14 names (so 5 rows are totally blank)... when I
run the macro... which looks like this:

Sub SortMe()
Cells.Sort Key1:=Range("G1"), Order1:=xlDescending, Header:=xlYes
End Sub

When I do it... it's odd... there are 5 names on top - the ones that
have no "points" so their total was nothing... then the 4 blank
rows... and THEN the rest of the people who had points, sorted highest
to lowest...

I don't understand why it's doing that? Did I do something wrong in
the formula or the macro?

Thanks!

 

[Pete_UK]

I didn't realise you were sorting by numeric values. You can make your
formula:

=SUM(B2:F2)

which will return zeros for those who have not scored any points. Now
when you sort in descending order the names with no points will appear
at the bottom of the list. To avoid the complete blanks at the bottom
of the list, make sure your sort range does not include them.

If you want the zeros to appear blank, you can apply conditional
formatting to the cells, such that if the cell contents is zero then
apply a foreground colour of white (assuming your background is also
white).

Hope this helps.

Pete

 

[yolanda.silva]

I didn't realise you were sorting by numeric values. You can make your
formula:

=SUM(B2:F2)

which will return zeros for those who have not scored any points. Now
when you sort in descending order the names with no points will appear
at the bottom of the list. To avoid the complete blanks at the bottom
of the list, make sure your sort range does not include them.

If you want the zeros to appear blank, you can apply conditional
formatting to the cells, such that if the cell contents is zero then
apply a foreground colour of white (assuming your background is also
white).

Hope this helps.

Pete












- Show quoted text -

Oooh... that is helpful! I didn't know you could format it to have a
background color if it's a certain criteria? Any suggestions how I
might go about doing that? That's more complicated than I've done
before... I was doing good to have the "IF" statements

 

[yolanda.silva]

Oooh... that is helpful! I didn't know you could format it to have a
background color if it's a certain criteria? Any suggestions how I
might go about doing that? That's more complicated than I've done
before... I was doing good to have the "IF" statements - Hide quoted text -

- Show quoted text -

OOOH!! Nevermind!! I found it!!! I made it work!! That's
incredible! I didn't even know that was possible!!!

THANK YOU!!!!!!!!!!!!!!!!!!!!!!

 

[Pete_UK]

I take it that you are happy with what you've ended up with, then !!
<bg>

Thanks for feeding back, Yolanda.

Pete

 

[yolanda.silva]

I take it that you are happy with what you've ended up with, then !!
<bg>

Thanks for feeding back, Yolanda.

Pete






- Show quoted text -

I am, indeed, quite pleased One last question! Do you know if
there is a way to make the macro work even if the worksheet is
protected? I have it locked so that no one can change (or erase) the
formula that will calculate the total points... but, doing this causes
the macro to ask me if I want to "debug" it everytime I try to run
it. It runs perfectly if I just unlock the spreadsheet...

Any thoughts?

I accidentally replied to author (your email address, I think) when I
first asked this - so you may see this pop into your email account if
you haven't already!

Thanks!
-Yolanda

 

[Pete_UK]

Hi Yolanda,

yes, I did get the message via email as well, but I'll respond here to
keep the thread going.

What you can do within the macro is to unprotect the sheet first (the
password is provided within the macro), then do what the macro is
meant to do, and then protect the sheet again at the end of the macro.
Once you are happy that everything is working, you can also protect
the macro with a password to prevent people from seeing your code.

However, security is not very tight within Excel, and a determined
user could easily get past your passwords. If security is a major
issue, then Excel is not the best platform to build an application on.

Hope this helps.

Pete

 

[yolanda.silva]

Hi Yolanda,

yes, I did get the message via email as well, but I'll respond here to
keep the thread going.

What you can do within the macro is to unprotect the sheet first (the
password is provided within the macro), then do what the macro is
meant to do, and then protect the sheet again at the end of the macro.
Once you are happy that everything is working, you can also protect
the macro with a password to prevent people from seeing your code.

However, security is not very tight within Excel, and a determined
user could easily get past your passwords. If security is a major
issue, then Excel is not the best platform to build an application on.

Hope this helps.

Pete








- Show quoted text -

Unfortunately, I've been a little busy with work today so I haven't
had a chance to check this until just now!

When you say I can have a macro unprotect and protect the sheet
again... is there something I could use for that? Would that be
something to ues the macro recorder thing for? Would I just have 3
separate macros? One for unlocking, one for sorting and then one for
locking it again?

I know I ask a ton of questions.. unfortunately, I have very little
experience with excel yet I seem to keep being the chosen one for
these spreadsheets! I've learned so much, though! These groups
have been tremendously helpful!! As for Excel not being secure...
there's no one who would attempt to find the data or passwords or
anything - having the macro lock/unlock the sheet will be security
enough. I only want it locked so they can't mess up the
formulas...

 

[Gord Dibben]

You can place the unprotect and protect inside your original macro.

Sub whatever()
ActiveSheet.Unprotect Password:="justme"

do your sorting stuff here

ActiveSheet.Protect Password:="justme"
End Sub


Gord Dibben MS Excel MVP