Creating a macro to sort a complex dynamic named reference
 

Hi,
I'm having trouble creating a macro that will sort a named reference. I have
data in Columns A-D that will constantly be adding rows at the bottom. I have
4 header rows at the top so the data does not start until A5. I have created
a named reference called "Totals" which looks like this:
"=OFFSET(Totals!$A$5,0,0,COUNTA(Totals!$A:$A)-3,4)"

I then tried the code below to sort it but am getting an error: "The sort
reference is not valid. Make sure that it's within the data you want to sort,
and the first Sort By blow isn't the same or blank."

Application.Goto Reference:="Totals"
Selection.Sort Key1:=Range("A5"), Order1:=xlAscending, Key2:=Range("C5") _
, Order2:=xlAscending, Header:=xlGuess, OrderCustom:=1, MatchCase:= _
False, Orientation:=xlTopToBottom

I've tried searching but so far haven't found any solutions for using named
references instead of defined blocks of cells for this problem.

Any help would be appreciated!

Thank you,
-Dan

Creating a macro to sort a complex dynamic named reference
 

Here is how I would do it...

Dim rngToSort As Range
Set rngToSort = ThisWorkbook.Names("Totals").RefersToRange

With rngToSort
.Sort Key1:=.Parent.Range("A5"), order1:=xlAscending, _
Key2:=.Parent.Range("C5"), order1:=xlAscending, _
Header:=xlNo
End With

--
HTH...

Jim Thomlinson


"Dan" wrote:

Hi,
I'm having trouble creating a macro that will sort a named reference. I have
data in Columns A-D that will constantly be adding rows at the bottom. I have
4 header rows at the top so the data does not start until A5. I have created
a named reference called "Totals" which looks like this:
"=OFFSET(Totals!$A$5,0,0,COUNTA(Totals!$A:$A)-3,4)"

I then tried the code below to sort it but am getting an error: "The sort
reference is not valid. Make sure that it's within the data you want to sort,
and the first Sort By blow isn't the same or blank."

Application.Goto Reference:="Totals"
Selection.Sort Key1:=Range("A5"), Order1:=xlAscending, Key2:=Range("C5") _
, Order2:=xlAscending, Header:=xlGuess, OrderCustom:=1, MatchCase:= _
False, Orientation:=xlTopToBottom

I've tried searching but so far haven't found any solutions for using named
references instead of defined blocks of cells for this problem.

Any help would be appreciated!

Thank you,
-Dan

Creating a macro to sort a complex dynamic named reference
 

Hi Jim,

Unfortunately I still get a runtime 1004 - "Application-defined or
object-defined error". My code looks like this:

Sub SortTotals()

Dim rngToSort As Range
Set rngToSort = ThisWorkbook.Names("Totals").RefersToRange

With rngToSort
..Sort Key1:=.Parent.Range("A5"), order1:=xlAscending, _
Key2:=.Parent.Range("C5"), order1:=xlAscending, _
Header:=xlNo
End With

End Sub

Any ideas on what might be causing this?
-Dan


"Jim Thomlinson" wrote:

Here is how I would do it...

Dim rngToSort As Range
Set rngToSort = ThisWorkbook.Names("Totals").RefersToRange

With rngToSort
.Sort Key1:=.Parent.Range("A5"), order1:=xlAscending, _
Key2:=.Parent.Range("C5"), order1:=xlAscending, _
Header:=xlNo
End With

--
HTH...

Jim Thomlinson


"Dan" wrote:

Hi,
I'm having trouble creating a macro that will sort a named reference. I have
data in Columns A-D that will constantly be adding rows at the bottom. I have
4 header rows at the top so the data does not start until A5. I have created
a named reference called "Totals" which looks like this:
"=OFFSET(Totals!$A$5,0,0,COUNTA(Totals!$A:$A)-3,4)"

I then tried the code below to sort it but am getting an error: "The sort
reference is not valid. Make sure that it's within the data you want to sort,
and the first Sort By blow isn't the same or blank."

Application.Goto Reference:="Totals"
Selection.Sort Key1:=Range("A5"), Order1:=xlAscending, Key2:=Range("C5") _
, Order2:=xlAscending, Header:=xlGuess, OrderCustom:=1, MatchCase:= _
False, Orientation:=xlTopToBottom

I've tried searching but so far haven't found any solutions for using named
references instead of defined blocks of cells for this problem.

Any help would be appreciated!

Thank you,
-Dan