Selecting a growing area?

[Terry Pinnell]

Not sure if I've worded that subject properly, but what I want is a
macro I can use to sort my spreadsheet each time after I've added more
rows to it. It's in cols A-L, but while today it has 103 rows,
tomorrow it may be 105 or whatever.

The straightforward macro I've just recorded (by selecting all rows)
looks as follows. How do I change that so that it allows for any
number of rows please?

Sub SortDate_Title()
'
' SortDate_Title Macro
' Macro recorded 26/02/2006 by Terry Pinnell
'

'
Rows("3:3").Select
Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select
Range("A3:L103").Select
Selection.Sort Key1:=Range("B4"), Order1:=xlDescending,
Key2:=Range("A4") _
, Order2:=xlAscending, Header:=xlGuess, OrderCustom:=1,
MatchCase:= _
False, Orientation:=xlTopToBottom
End Sub

Any help much appreciated please.

 

[Bob Phillips]

Sub SortDate_Title()
'
' SortDate_Title Macro
' Macro recorded 26/02/2006 by Terry Pinnell
'

Dim rng As Range

Set rng = Range("A3").Resize(Cells(Rows.Count, "A").End(xlUp).Row -
2).EntireRow
rng.Sort Key1:=Range("B4"), _
Order1:=xlDescending, _
Key2:=Range("A4"), _
Order2:=xlAscending, _
Header:=xlGuess, _
OrderCustom:=1, _
MatchCase:=False, _
Orientation:=xlTopToBottom
End Sub




--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

 

[Terry Pinnell]

Sub SortDate_Title()
'
' SortDate_Title Macro
' Macro recorded 26/02/2006 by Terry Pinnell
'

Dim rng As Range

Set rng = Range("A3").Resize(Cells(Rows.Count, "A").End(xlUp).Row -
2).EntireRow
rng.Sort Key1:=Range("B4"), _
Order1:=xlDescending, _
Key2:=Range("A4"), _
Order2:=xlAscending, _
Header:=xlGuess, _
OrderCustom:=1, _
MatchCase:=False, _
Orientation:=xlTopToBottom
End Sub

Brilliant - thanks Bob!

 

[Bob Phillips]

Pleasure Terry.

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)