sort macro

[Randy Starkey]

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

 

[RB Smissaert]

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

 

[Dave Peterson]

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.

 

[Leith Ross]

Hello Randy,

REMOVE THIS:

Range("A:A").Select

USE THIS:

ActiveSheet.Cells(Rows.Count, "A").End(xlUp).Select

Hope this helps,
Leith Ross

 

[Randy Starkey]

Thanks much for both code samples! We use exclusively Office 2003 so the
earlier versions are not an issue.

--Randy Starkey

 

[Randy Starkey]

Leith,

My purpose in this statement was just to move the cursor to A3 after the
sort. Is it wrong?

Thanks!

--Randy