Macro Problem

Is there a way to write a macro that will expand its range.
I am working on a simple progam that will automaticaly sort a group of employees by hours worked. My problem occurs when I get a new employee into the group. I insert a row and add the employee. The old macro has to be modified manually. Is there a way to have the macro expand its range automatically as I insert a row?

Please see the simple macro below.

If I insert a row, I will have to change the range from "E8:H17" to Range("E8:H18").

Sub Sort()
'
' Sort Macro
' Macro recorded 3/2/2002 by Jerry Russell
'

'
Range("E8:H17").Select
Selection.Sort Key1:=Range("H8"), Order1:=xlDescending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
Range("J13").Select
End Sub


Thanks for any help!

[Tom Urtis]

Try decaring a variable for the range and assigning it to a dynamic reference. Here's your macro using that approach:

''''''''''''''''''''''''''''''

Sub Sort()

Application.ScreenUpdating = False

Dim myRange As Range
Set myRange = Range("E8", Range("H65536").End(xlUp))

myRange.Sort Key1:=Range("H8"), Order1:=xlDescending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom

Range("J13").Select

Application.ScreenUpdating = True

End Sub

''''''''''''''''''''''''''''''

I added the screen updating lines just in case your range gets larger, though at this early stage it's negligible.

If you have data among columns E:H, in rows above 65536 but below where you know you will not expand the range, then adjust that reference in line 3, from 65536 to some lesser row number.

Tom Urtis