Editing Named Range

[Coop913]

On a worksheet named "Hidden", I have the following code, which takes several three-column groups and sorts them. Another worksheet then grabs the results.

Private Sub Worksheet_Activate()

With ActiveSheet
With .Range("Hits")
.Sort key1:="Hits", order1:=xlDescending, _
Header:=xlYes
End With
With .Range("HR")
.Sort key1:="HR", order1:=xlDescending, _
Header:=xlYes
End With
With .Range("Doubles")
.Sort key1:="2B", order1:=xlDescending, _
Header:=xlYes
End With
With .Range("Triples")
.Sort key1:="3B", order1:=xlDescending, _
Header:=xlYes
End With
With .Range("RBIs")
.Sort key1:="RBIs", order1:=xlDescending, _
Header:=xlYes
End With
With .Range("Runs")
.Sort key1:="Runs", order1:=xlDescending, _
Header:=xlYes
End With
With .Range("Walks")
.Sort key1:="BB", order1:=xlDescending, _
Header:=xlYes
End With
With .Range("SO")
.Sort key1:="SO", order1:=xlDescending, _
Header:=xlYes
End With
With .Range("SB")
.Sort key1:="SB", order1:=xlDescending, key2:="CS", order1:=xlAscending, _
Header:=xlYes
End With
With .Range("Wins")
.Sort key1:="W", order1:=xlDescending, key2:="L", order1:=xlAscending, _
Header:=xlYes
End With
With .Range("Saves")
.Sort key1:="S", order1:=xlDescending, _
Header:=xlYes
End With
With .Range("IP")
.Sort key1:="IP", order1:=xlDescending, _
Header:=xlYes
End With
With .Range("SOP")
.Sort key1:="SO", order1:=xlDescending, _
Header:=xlYes
End With
With .Range("Games")
.Sort key1:="G", order1:=xlDescending, _
Header:=xlYes
End With
With .Range("CG")
.Sort key1:="CG", order1:=xlDescending, _
Header:=xlYes
End With
With .Range("SHO")
.Sort key1:="SHO", order1:=xlDescending, _
Header:=xlYes
End With
With .Range("AVG")
.Sort key1:="AVG", order1:=xlDescending, _
Header:=xlYes
End With
With .Range("OBP")
.Sort key1:="OBP", order1:=xlDescending, _
Header:=xlYes
End With
With .Range("SLG")
.Sort key1:="SLG", order1:=xlDescending, _
Header:=xlYes
End With
With .Range("ERA")
.Sort key1:="ERA", order1:=xlAscending, _
Header:=xlYes
End With
End With
End Sub

My problem is that I want to extend the range to include more rows at the bottom of these named ranges. I don't know how to do that, nor even how to find the code (whatever) that a friend used to define these ranges a couple of years ago.

Can anyone please help me with this?

Thanks in advance,
Jack

 

[Joe4]

You haven't really described how your data is laid out (all on the same sheet or on different sheets), but you can re-define your named range to include the new data.

Here is one method that uses Current Region that selects all contiguous cells from a given starting cell.

ActiveWorkbook.Names.Add Name:="MyRange", RefersTo:=Range("A1").CurrentRegion

So starting in cell A1, it will select all surrounding cells up to the first blank cell/row combination.

If this does not work for you, please describe your data structure and needs in more detail.