Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Variable Range Sort
Hello I am trying to setup a macro to sort a variable range. I need to sort
from A3 to L, but the L row would be variable... I know if I select A3:L3 and then do End(xlDown) that would be a start but I am not sure how exactly to work this into the sort portion..... A start is.... Range("A3:L3").Select Range(Selection, Selection.End(xlDown)).Select but I am stuck now... I can't figure out how to set the range in sort to something like that. I need to sort ascending with column E then secondary with column C. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Variable Range Sort
Dim wks as worksheet
dim LastRow as long dim myRng as range set wks = worksheets("SomeSheetNameHere") 'or activesheet '??? with wks lastrow = .cells(.rows.count,"A").end(xlup).row set myrng = .range("A3:L" & lastrow) end with with myrng .cells.sort _ Key1:=.Columns(5), Order1:=xlAscending, _ key2:=.columns(3), order2:=xldescending, _ Header:=xlno, _ OrderCustom:=1, MatchCase:=False, _ Orientation:=xlTopToBottom end with I used column A to determine the last used row. You may need to change this. And I specified that the range did not have headers. Change that if row 3 is a header row. But don't let excel guess. You know your data better than excel -- and excel can guess wrong! Brian wrote: Hello I am trying to setup a macro to sort a variable range. I need to sort from A3 to L, but the L row would be variable... I know if I select A3:L3 and then do End(xlDown) that would be a start but I am not sure how exactly to work this into the sort portion..... A start is.... Range("A3:L3").Select Range(Selection, Selection.End(xlDown)).Select but I am stuck now... I can't figure out how to set the range in sort to something like that. I need to sort ascending with column E then secondary with column C. -- Dave Peterson |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Variable Range Sort
Oh I forgot to add onto this.... I am working it into this macro which I
could use a tab of help with to not mess up the sorting too.... I need to change ActiveSheet.Rows("5:5").Select to be variable also to select the last filled out row... how could i select the row of the selected cell? I could select the last cell at the end of the column with... ActiveSheet.Range("A3").End(xlDown).Offset(1, 0).Select The reason I need to do it this way is because below all of this in column A there is additional data I don't want to sort or insert with. Sub InsertSheets() Dim cnt As Integer Dim pwrd As String pwrd = "XXXX" cnt = 1 CntWanted = 0 CntWanted = CntWanted + InputBox("How many rows would you like to add?") ActiveSheet.Unprotect pwrd Do ActiveSheet.Rows("5:5").Select Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromAbove cnt = cnt + 1 Loop Until cnt = CntWanted ActiveSheet.Protect pwrd End Sub "Brian" wrote: Hello I am trying to setup a macro to sort a variable range. I need to sort from A3 to L, but the L row would be variable... I know if I select A3:L3 and then do End(xlDown) that would be a start but I am not sure how exactly to work this into the sort portion..... A start is.... Range("A3:L3").Select Range(Selection, Selection.End(xlDown)).Select but I am stuck now... I can't figure out how to set the range in sort to something like that. I need to sort ascending with column E then secondary with column C. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Variable Range Sort
Close, but it keeps sorting the rows below also because I have some data
under the end of the data that is entered in. It needs to only sort A3:L and the end of the data at that point. If I for example highlight A3:L3 and do ctrl shift down it get's to the end of my data that I want to sort and stops prior to data below that does not need to be sorted. "Dave Peterson" wrote: Dim wks as worksheet dim LastRow as long dim myRng as range set wks = worksheets("SomeSheetNameHere") 'or activesheet '??? with wks lastrow = .cells(.rows.count,"A").end(xlup).row set myrng = .range("A3:L" & lastrow) end with with myrng .cells.sort _ Key1:=.Columns(5), Order1:=xlAscending, _ key2:=.columns(3), order2:=xldescending, _ Header:=xlno, _ OrderCustom:=1, MatchCase:=False, _ Orientation:=xlTopToBottom end with I used column A to determine the last used row. You may need to change this. And I specified that the range did not have headers. Change that if row 3 is a header row. But don't let excel guess. You know your data better than excel -- and excel can guess wrong! Brian wrote: Hello I am trying to setup a macro to sort a variable range. I need to sort from A3 to L, but the L row would be variable... I know if I select A3:L3 and then do End(xlDown) that would be a start but I am not sure how exactly to work this into the sort portion..... A start is.... Range("A3:L3").Select Range(Selection, Selection.End(xlDown)).Select but I am stuck now... I can't figure out how to set the range in sort to something like that. I need to sort ascending with column E then secondary with column C. -- Dave Peterson . |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Variable Range Sort
Got it... changed LastRow to be....
LastRow = .Range("A3").End(xlDown).Row "Brian" wrote: Close, but it keeps sorting the rows below also because I have some data under the end of the data that is entered in. It needs to only sort A3:L and the end of the data at that point. If I for example highlight A3:L3 and do ctrl shift down it get's to the end of my data that I want to sort and stops prior to data below that does not need to be sorted. "Dave Peterson" wrote: Dim wks as worksheet dim LastRow as long dim myRng as range set wks = worksheets("SomeSheetNameHere") 'or activesheet '??? with wks lastrow = .cells(.rows.count,"A").end(xlup).row set myrng = .range("A3:L" & lastrow) end with with myrng .cells.sort _ Key1:=.Columns(5), Order1:=xlAscending, _ key2:=.columns(3), order2:=xldescending, _ Header:=xlno, _ OrderCustom:=1, MatchCase:=False, _ Orientation:=xlTopToBottom end with I used column A to determine the last used row. You may need to change this. And I specified that the range did not have headers. Change that if row 3 is a header row. But don't let excel guess. You know your data better than excel -- and excel can guess wrong! Brian wrote: Hello I am trying to setup a macro to sort a variable range. I need to sort from A3 to L, but the L row would be variable... I know if I select A3:L3 and then do End(xlDown) that would be a start but I am not sure how exactly to work this into the sort portion..... A start is.... Range("A3:L3").Select Range(Selection, Selection.End(xlDown)).Select but I am stuck now... I can't figure out how to set the range in sort to something like that. I need to sort ascending with column E then secondary with column C. -- Dave Peterson . |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Variable Range Sort
Thanks so much! I worked some of that into my other question in this
also..... Final result which correctly inserts rows and sorts.... Sub InsertSheets() Dim cnt As Integer Dim pwrd As String Dim wks As Worksheet Dim LastRow As Long Dim myRng As Range pwrd = "XXXX" cnt = 0 Set wks = ActiveSheet With wks LastRow = .Range("A3").End(xlDown).Row Set myRng = .Range("A3:L" & LastRow) End With CntWanted = 0 CntWanted = CntWanted + InputBox("How many rows would you like to add?") ActiveSheet.Unprotect pwrd Do wks.Rows(LastRow + 1).Select Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromAbove cnt = cnt + 1 Loop Until cnt = CntWanted With myRng .Cells.Sort _ Key1:=.Columns(5), Order1:=xlAscending, _ key2:=.Columns(3), order2:=xlDescending, _ Header:=xlNo, _ OrderCustom:=1, MatchCase:=False, _ Orientation:=xlTopToBottom End With ActiveSheet.Protect pwrd End Sub "Dave Peterson" wrote: Dim wks as worksheet dim LastRow as long dim myRng as range set wks = worksheets("SomeSheetNameHere") 'or activesheet '??? with wks lastrow = .cells(.rows.count,"A").end(xlup).row set myrng = .range("A3:L" & lastrow) end with with myrng .cells.sort _ Key1:=.Columns(5), Order1:=xlAscending, _ key2:=.columns(3), order2:=xldescending, _ Header:=xlno, _ OrderCustom:=1, MatchCase:=False, _ Orientation:=xlTopToBottom end with I used column A to determine the last used row. You may need to change this. And I specified that the range did not have headers. Change that if row 3 is a header row. But don't let excel guess. You know your data better than excel -- and excel can guess wrong! Brian wrote: Hello I am trying to setup a macro to sort a variable range. I need to sort from A3 to L, but the L row would be variable... I know if I select A3:L3 and then do End(xlDown) that would be a start but I am not sure how exactly to work this into the sort portion..... A start is.... Range("A3:L3").Select Range(Selection, Selection.End(xlDown)).Select but I am stuck now... I can't figure out how to set the range in sort to something like that. I need to sort ascending with column E then secondary with column C. -- Dave Peterson . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Range to Variable for Sort | Excel Programming | |||
Assign range to variable for sort | Excel Programming | |||
Sort by a Variable Range | Excel Programming | |||
VBA Variable Range Sort | Excel Discussion (Misc queries) | |||
Sort variable range in descending order | Excel Programming |