Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
sort macro
Hi,
Here's a sort macro I recorded. It works, but I'd like to change it to where the bottom row it goes to (the column value is constant) is the last row with content in column A. ================================ Sub Hospital_Sheet_Sort() ' ' Hospital_Sheet_Sort Macro ' Macro recorded 3/19/2005 by Randy Starkey ' ' Range("A3:V361").Select Range("V361").Activate Selection.Sort Key1:=Range("A3"), Order1:=xlAscending, Key2:=Range("N3") _ , Order2:=xlDescending, Header:=xlNo, OrderCustom:=1, MatchCase:=False _ , Orientation:=xlTopToBottom, DataOption1:=xlSortNormal, DataOption2:= _ xlSortNormal Range("A3:A3").Select End Sub ================================= Could someone help me making that adjustment? Thanks! --Randy Starkey |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
sort macro
Sub Hospital_Sheet_Sort()
' ' Hospital_Sheet_Sort Macro ' Macro recorded 3/19/2005 by Randy Starkey ' ' Range(Cells(3, 1), Cells(Cells(65536, 1).End(xlUp).Row, 22)).Sort _ Key1:=Range("A3"), _ Order1:=xlAscending, _ Key2:=Range("N3"), _ Order2:=xlDescending, _ Header:=xlNo, _ OrderCustom:=1, _ MatchCase:=False, _ Orientation:=xlTopToBottom End Sub I have left DataOption1 etc. as I think this won't work with earlier versions of Excel. RBS "Randy Starkey" wrote in message ... Hi, Here's a sort macro I recorded. It works, but I'd like to change it to where the bottom row it goes to (the column value is constant) is the last row with content in column A. ================================ Sub Hospital_Sheet_Sort() ' ' Hospital_Sheet_Sort Macro ' Macro recorded 3/19/2005 by Randy Starkey ' ' Range("A3:V361").Select Range("V361").Activate Selection.Sort Key1:=Range("A3"), Order1:=xlAscending, Key2:=Range("N3") _ , Order2:=xlDescending, Header:=xlNo, OrderCustom:=1, MatchCase:=False _ , Orientation:=xlTopToBottom, DataOption1:=xlSortNormal, DataOption2:= _ xlSortNormal Range("A3:A3").Select End Sub ================================= Could someone help me making that adjustment? Thanks! --Randy Starkey |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
sort macro
Something like:
Option Explicit Sub Hospital_Sheet_Sort() Dim myRng As Range With ActiveSheet Set myRng = .Range("a3:V" & .Cells(.Rows.Count, "A").End(xlUp).Row) myRng.Sort Key1:=.Range("A3"), Order1:=xlAscending, _ Key2:=.Range("N3"), Order2:=xlDescending, _ Header:=xlNo, OrderCustom:=1, MatchCase:=False, _ Orientation:=xlTopToBottom, _ DataOption1:=xlSortNormal, DataOption2:=xlSortNormal End With End Sub This also avoids selecting any cells--and that's usually a good thing. Just a word of warning. Those DataOption# were added in xl2002. If you think you're going to need to use this in xl2k or below, remove them from the code. Randy Starkey wrote: Hi, Here's a sort macro I recorded. It works, but I'd like to change it to where the bottom row it goes to (the column value is constant) is the last row with content in column A. ================================ Sub Hospital_Sheet_Sort() ' ' Hospital_Sheet_Sort Macro ' Macro recorded 3/19/2005 by Randy Starkey ' ' Range("A3:V361").Select Range("V361").Activate Selection.Sort Key1:=Range("A3"), Order1:=xlAscending, Key2:=Range("N3") _ , Order2:=xlDescending, Header:=xlNo, OrderCustom:=1, MatchCase:=False _ , Orientation:=xlTopToBottom, DataOption1:=xlSortNormal, DataOption2:= _ xlSortNormal Range("A3:A3").Select End Sub ================================= Could someone help me making that adjustment? Thanks! --Randy Starkey -- Dave Peterson |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
sort macro
Thanks much for both code samples! We use exclusively Office 2003 so the
earlier versions are not an issue. --Randy Starkey "Dave Peterson" wrote in message ... Something like: Option Explicit Sub Hospital_Sheet_Sort() Dim myRng As Range With ActiveSheet Set myRng = .Range("a3:V" & .Cells(.Rows.Count, "A").End(xlUp).Row) myRng.Sort Key1:=.Range("A3"), Order1:=xlAscending, _ Key2:=.Range("N3"), Order2:=xlDescending, _ Header:=xlNo, OrderCustom:=1, MatchCase:=False, _ Orientation:=xlTopToBottom, _ DataOption1:=xlSortNormal, DataOption2:=xlSortNormal End With End Sub This also avoids selecting any cells--and that's usually a good thing. Just a word of warning. Those DataOption# were added in xl2002. If you think you're going to need to use this in xl2k or below, remove them from the code. Randy Starkey wrote: Hi, Here's a sort macro I recorded. It works, but I'd like to change it to where the bottom row it goes to (the column value is constant) is the last row with content in column A. ================================ Sub Hospital_Sheet_Sort() ' ' Hospital_Sheet_Sort Macro ' Macro recorded 3/19/2005 by Randy Starkey ' ' Range("A3:V361").Select Range("V361").Activate Selection.Sort Key1:=Range("A3"), Order1:=xlAscending, Key2:=Range("N3") _ , Order2:=xlDescending, Header:=xlNo, OrderCustom:=1, MatchCase:=False _ , Orientation:=xlTopToBottom, DataOption1:=xlSortNormal, DataOption2:= _ xlSortNormal Range("A3:A3").Select End Sub ================================= Could someone help me making that adjustment? Thanks! --Randy Starkey -- Dave Peterson |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
sort macro
Hello Randy, REMOVE THIS: Range("A:A").Select USE THIS: ActiveSheet.Cells(Rows.Count, "A").End(xlUp).Select Hope this helps, Leith Ross -- Leith Ross ------------------------------------------------------------------------ Leith Ross's Profile: http://www.excelforum.com/member.php...o&userid=18465 View this thread: http://www.excelforum.com/showthread...hreadid=355849 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
sort macro
Leith,
My purpose in this statement was just to move the cursor to A3 after the sort. Is it wrong? Thanks! --Randy "Leith Ross" wrote in message ... Hello Randy, REMOVE THIS: Range("A:A").Select USE THIS: ActiveSheet.Cells(Rows.Count, "A").End(xlUp).Select Hope this helps, Leith Ross -- Leith Ross ------------------------------------------------------------------------ Leith Ross's Profile: http://www.excelforum.com/member.php...o&userid=18465 View this thread: http://www.excelforum.com/showthread...hreadid=355849 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Sort Macro | Excel Discussion (Misc queries) | |||
Sort Macro | Excel Worksheet Functions | |||
Macro Help Needed - Excel 2007 - Print Macro with Auto Sort | Excel Worksheet Functions | |||
Using Macro to sort without clicking on macro button | Excel Discussion (Misc queries) | |||
Sort Macro | Excel Discussion (Misc queries) |