Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
dynamic sort
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
dynamic sort
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 on whether or not row 2 is a header/label or not. Prevents surprises. "jeffatwork" wrote: 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 . |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
dynamic sort
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 jeffatwork wrote: 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 -- Dave Peterson |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
dynamic sort
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. "jeffatwork" wrote: 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 . |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
dynamic sort
Thanks guys, you ROCK! They both work.
" 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
sort dynamic range on active cell | Excel Programming | |||
Sort Dynamic Range | Excel Programming | |||
dynamic sort macro across 3 linked sheets | Excel Discussion (Misc queries) | |||
Dynamic Sort Macro | Excel Programming | |||
Dynamic Sort | Excel Programming |