Automatic re-sorting of data

[K. Georgiadis]

I have a large data list in range A2:P76 of Sheet1 (with row 2 containing the column headings) and the list is sorted in descending order according to the values in column P.

There is the strong likelihood that the data will change several times, requiring resorting of the list. Is it possible to have VBA code in Sheet1 that automatically resorts each time the sheet is activated, or am I better off recording a macro and providing the user with a resorting button?

[Jacob Hilderbrand]

Try this:



Option Explicit

Private Sub Worksheet_Activate()
Range("A2:P76").Sort Key1:=Range("P2"), Order1:=xlDescending, Header:=xlYes, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
End Sub

[TheAntiGates]

That sort is nearly instantaneous, isn't it? So the worst cost is probably a "blinking" or perhaps the "Want to save?" prompt when nothing has changed. That would weigh against the cost of the user "not liking" using the button.

The same code you would record for a button would be what you put in the Activate event code, as DRJ shows above.

Whatever is coded, putting A2:P76 in the code might bite you later. Define a named range over that rectangle, and refer to that in the code, if there's a chance the dimensions will change.

[K. Georgiadis]

Thanks DRJ, I'll give it a try. Also, thanks "The AntiGates;" excvellent point about using a named range

[Jacob Hilderbrand]

You're Welcome :beerchug:

Take Care

[K. Georgiadis]

I thought that using a dynamic named range was a good idea, but then I discovered I didn't really know how to use it in the code provided by DRJ :(

My sheet containing the code for an automatic re-sort is now called "Matrix" and the data in A2:P76 is named "Dynarange," defined by the following formula:

=OFFSET(Matrix!$A$2,0,0,COUNTA(Matrix!$A:$A,16))

16 being the number of columns in the data list. I often use this type of formula for named ranges that govern Pivot Tables, so that the Pivot Tables automatically adjust for data additions to a list.

I quickly found out that I cannot simply replace "Dynarange" for "A2:P76" on line 1 of DRJ's code and leave everything else the same. Is different syntax needed for a dynamic range?
Does Dyanarange need to be declared as variant?

[Jacob Hilderbrand]

It should work fine, but your Dynamic Range is incorrect. The 16 should not be inside the CountA formula.


Option Explicit

Private Sub Worksheet_Activate()

Range("Dynarange").Sort Key1:=Range("P2"), Order1:=xlDescending, Header:=xlYes, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal

End Sub


=OFFSET(Matrix!$A$2,0,0,COUNTA(Matrix!$A:$A),16)

[K. Georgiadis]

Duh!!!! You're absolutely correct (as usual). I typed the formula incorrectly -- haste makes waste :banghead: Thanks!!!