By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
464,789 Members | 1,433 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 464,789 IT Pros & Developers. It's quick & easy.

Switch sort direction each time macro is invoked

P: 3
Hello all

I created a button with excel and assigned a macro to it which I recorded with excel Macro Record..

The button sorts column C ascending, this is the code

Expand|Select|Wrap|Line Numbers
  1. Sub Macro1()
  2.     Range("C7:C11").Select
  3.     Selection.Sort Key1:=Range("C7"), Order1:=xlAscending, Header:=xlGuess, 
  4.         OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
  5.         DataOption1:=xlSortNormal
  6.     Range("D9").Select
  7. End Sub
I want to make this macro sorts Descending when I click on the button again ..
and the Ascending with another click and so on ..

If anyone knows how to do it .. please write the code ..

thanks
Feb 8 '08 #1
Share this Question
Share on Google+
5 Replies

P: 58
Expand|Select|Wrap|Line Numbers
  1. Sub Macro1_R1()
  2. Dim lngWay As Long
  3. If Range("C11").Value > Range("C7").Value Then
  4.    lngWay = xlDescending
  5. Else
  6.    lngWay = xlAscending
  7. End If
  8. Range("C7:C11").Sort Key1:=Range("C7"), Order1:=lngWay, _
  9.                 Header:=xlGuess, OrderCustom:=1, MatchCase:=False, _
  10.                 Orientation:=xlTopToBottom, DataOption1:=xlSortNormal
  11. Range("D9").Select
  12. End Sub

Hello all

I created a button with excel and assigned a macro to it which I recorded with excel Macro Record..

The button sorts column C ascending, this is the code

Sub Macro1()
Range("C7:C11").Select
Selection.Sort Key1:=Range("C7"), Order1:=xlAscending, Header:=xlGuess,
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
Range("D9").Select
End Sub


I want to make this macro sorts Descending when I click on the button again ..
and the Ascending with another click and so on ..

If anyone knows how to do it .. please write the code ..

thanks
Feb 9 '08 #2

P: 3
Sub Macro1_R1()
Dim lngWay As Long
If Range("C11").Value > Range("C7").Value Then
lngWay = xlDescending
Else
lngWay = xlAscending
End If
Range("C7:C11").Sort Key1:=Range("C7"), Order1:=lngWay, _
Header:=xlGuess, OrderCustom:=1, MatchCase:=False, _
Orientation:=xlTopToBottom, DataOption1:=xlSortNormal
Range("D9").Select
End Sub

THANKS UBENTOOK !!!!
Feb 9 '08 #3

P: 3
I want to use this sub with a different column and i want to know what's the function of this so that i can change it to suit the new table :

Range("D9").Select
Feb 11 '08 #4

kadghar
Expert 100+
P: 1,295
I want to use this sub with a different column and i want to know what's the function of this so that i can change it to suit the new table :

Range("D9").Select
Use WORKBOOKS(index), WORKSHEETS(index) and CELLS(row, col), if you dont specify one of the first two, it'll use the active one.

e.g.

workbooks(1).worksheets(2).cells(9,4) will refer to the cell D9.

You can put them into a With to make things easier, and use ranges with cells:

Expand|Select|Wrap|Line Numbers
  1. with workbooks(1).worksheets(2)
  2.     range(.cells(9,4), .cells(15,5)) = "hi"
  3. end with
or use the name of the book/sheet/range, instead of the index number:

workbooks("mybook").worksheets("sheet1").range("B9 :F17")

and this names are strings, so you can modify them as any other string.

HTH
Feb 11 '08 #5

P: 58
Range("D9").Select is not required; you can delete it.
'--
The code sorts in Column C.
To have the code work on a different column then...
where one or more upper case C's follows the word Range, change C to the letter of the new column.
'--


I want to use this sub with a different column and i want to know what's the function of this so that i can change it to suit the new table :

Range("D9").Select
Feb 12 '08 #6

Post your reply

Sign in to post your reply or Sign up for a free account.