dynamic sort

[jeffatwork]

How do I create a dynamic variable to sort my data. I'm attempting to creat
buttons to sort by last name, total_score, ID, location.

Thought if there was a way to set a variable (my sort range), I could then
sort it on any of those criteria. Hope that makes sense.

its this first part after the word Range ( ??????) instead of "A2:O120" I
want it to find the last row and create that Range as a variable

Range("A2:O120").Sort Key1:=Range("E2"), Order1:=xlAscending, Header:= _
xlGuess, OrderCustom:=1, MatchCase:=False,
Orientation:=xlTopToBottom, _
DataOption1:=xlSortTextAsNumbers

Sorry, new to this stuff..

Thanks in advance

Jeff

 

[JLatham]

It does make sense and it's not too difficult to do. You need 2 range
objects to set up to make the sort dynamic; one to reference the range to be
sorted and one to reference the sort key. Also, decide on one column that
will ALWAYS have an entry in it for all rows to be sorted, even if it's the
sort key column. You can have your buttons simply pass the Key1 address to a
'generic' sort routine like this. This code assumes the sheet with the data
to be sorted is currently selected/active, and that both of the Sub's below
are in the same code module.

Sub Button1_Click()
DynSort "E2"
End Sub

Sub DynSort(sKeyAddress As String)
Dim sortRange As Range
Dim sKey1 As Range
Dim lastRow as Long
Const TestCol = "A"
Const firstColToSort = "A"
Const lastColToSort = "O"
Const firstRowToSort = 2

lastRow = Range(TestCol & Rows.Count).End(xlUp).Row
If lastRow < firstRowToSort Then
Exit Sub ' nothing to sort
End If
Set sortRange = Range(firstColToSort & firstRowToSort & ":" & _
lastColToSort & lastRow)
Set sKey1 = Range(sKeyAddress)

sortRange.Sort Key1:=sKey1, Order1:=xlAscending, Header:= _
xlGuess, OrderCustom:=1, MatchCase:=False,
Orientation:=xlTopToBottom, _
DataOption1:=xlSortTextAsNumbers
End Sub

By the way, instead of Header:=xlGuess, I would use xlYes or xlNo depending

 

[Dave Peterson]

I like to pick out a column that always has data in it if the row is used. If
that works for you, then maybe something like:


Dim myRng as range
dim wks as worksheet
dim LastRow as long

set wks = worksheets("Sheet1")

with wks
'I used column A
lastrow = .cells(.rows.count,"A").end(xlup).row
set myrng = .range("A2:O" & lastrow)
end with

with myrng
.sort key1:=.columns(5), ....
end with

 

[JLatham]

Added notes: I used separate Const values for the columns involved and the
first data row so that the routine can be re-used in the future in other
projects with very little 'attention' from you, just change those Const
values and you have a single field sort routine that works in many different
places. Also by recording macros for 2 and 3 field sorts you can see how to
easily adapt it to those situations.

Two lines of code I left out that could go just before the End Sub are these:
Set sKey1 = Nothing
Set sortRange = Nothing

Those explicitely release those resources back to the system and help
prevent potential "memory leaks", just part of good housekeeping.

 

[Joe Schmoe]

Thanks guys, you ROCK! They both work.



"