| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sort Without Knowing Last Cell
Hi everyone,
I have a program that outputs columns of values to a worksheet. I have titles in row 2 and the values start in columns "E3:G?", "I3:L?" etc. I would like to sort the values but I don't know what the last row will be. Here is an example for columns "E3:G?" ... Range("E3:G?").Select Selection.Sort Key1:=Range("G3"), Order1:=xlDescending, Key2:=Range("E3") _ , Order2:=xlAscending, Key3:=Range("F3"), Order3:=xlAscending .... with the ? is the unknown number to sort down to. Thanks in Advance. All the Best. Paul |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Sort Without Knowing Last Cell
That depends on whether the columns you want to include in your sort are
fully populated. If you know that column G will always be fully populated right to the bottom then: lastrow=range("G3").End(xlDown).Row Range("E3:G" & lastrow).Select should do it. If it's column E you know to be fully populated then replace "G3" with "E3" If none of the rows is always guaranteed to be fully populated then you might try lastrow=range("E3").CurrentRegion.Row+range("E3"). CurrentRegion.Rows.Count-1 instead. p45cal -- p45cal "Paul Black" wrote: Hi everyone, I have a program that outputs columns of values to a worksheet. I have titles in row 2 and the values start in columns "E3:G?", "I3:L?" etc. I would like to sort the values but I don't know what the last row will be. Here is an example for columns "E3:G?" ... Range("E3:G?").Select Selection.Sort Key1:=Range("G3"), Order1:=xlDescending, Key2:=Range("E3") _ , Order2:=xlAscending, Key3:=Range("F3"), Order3:=xlAscending .... with the ? is the unknown number to sort down to. Thanks in Advance. All the Best. Paul |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Sort Without Knowing Last Cell
BTW, you don't need to select first, you can go straight into
Range("E3:G" & lastrow).Sort.... -- p45cal "p45cal" wrote: That depends on whether the columns you want to include in your sort are fully populated. If you know that column G will always be fully populated right to the bottom then: lastrow=range("G3").End(xlDown).Row Range("E3:G" & lastrow).Select should do it. If it's column E you know to be fully populated then replace "G3" with "E3" If none of the rows is always guaranteed to be fully populated then you might try lastrow=range("E3").CurrentRegion.Row+range("E3"). CurrentRegion.Rows.Count-1 instead. p45cal -- p45cal "Paul Black" wrote: Hi everyone, I have a program that outputs columns of values to a worksheet. I have titles in row 2 and the values start in columns "E3:G?", "I3:L?" etc. I would like to sort the values but I don't know what the last row will be. Here is an example for columns "E3:G?" ... Range("E3:G?").Select Selection.Sort Key1:=Range("G3"), Order1:=xlDescending, Key2:=Range("E3") _ , Order2:=xlAscending, Key3:=Range("F3"), Order3:=xlAscending .... with the ? is the unknown number to sort down to. Thanks in Advance. All the Best. Paul |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Sort Without Knowing Last Cell
A few questions:
Does your table have headers in row 2? Is your table clear of internal blank rows? Are there other tables or data below your desired sortable range/table? Assuming the answers are, Yes, Yes, No (as it should), then: Sub Dsort() Dim SortRng As Range Set SortRng = Range("E2:G" & Rows.Count) SortRng.Sort Key1:=Range("G2"), Order1:=xlDescending, Header:=xlYes, _ Key2:=Range("E2"), Order2:=xlAscending, Header:=xlYes, _ Key3:=Range("F2"), Order3:=xlAscending, Header:=xlYes End Sub Regards Robert McCurdy "Paul Black" wrote in message oups.com... Hi everyone, I have a program that outputs columns of values to a worksheet. I have titles in row 2 and the values start in columns "E3:G?", "I3:L?" etc. I would like to sort the values but I don't know what the last row will be. Here is an example for columns "E3:G?" ... Range("E3:G?").Select Selection.Sort Key1:=Range("G3"), Order1:=xlDescending, Key2:=Range("E3") _ , Order2:=xlAscending, Key3:=Range("F3"), Order3:=xlAscending .... with the ? is the unknown number to sort down to. Thanks in Advance. All the Best. Paul |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Sort Without Knowing Last Cell
Thanks for the reply p45cal,
I had just worked out doing it this way ... Range("E3:G3").End(xlDown).Sort _ Key1:=Range("G3"), Order1:=xlDescending, _ Key2:=Range("E3"), Order2:=xlAscending, _ Key3:=Range("F3"), Order3:=xlAscending .... and ... Range("I3:L3").End(xlDown).Sort _ Key1:=Range("L3"), Order1:=xlDescending, _ Key2:=Range("I3"), Order2:=xlAscending, _ Key3:=Range("J3"), Order3:=xlAscending .... are you saying that this is NOT the optimum way to do it?. Thanks in Advance. All the Best. Paul On Aug 31, 11:19 am, "Robert McCurdy" wrote: A few questions: Does your table have headers in row 2? Is your table clear of internal blank rows? Are there other tables or data below your desired sortable range/table? Assuming the answers are, Yes, Yes, No (as it should), then: Sub Dsort() Dim SortRng As Range Set SortRng = Range("E2:G" & Rows.Count) SortRng.Sort Key1:=Range("G2"), Order1:=xlDescending, Header:=xlYes, _ Key2:=Range("E2"), Order2:=xlAscending, Header:=xlYes, _ Key3:=Range("F2"), Order3:=xlAscending, Header:=xlYes End Sub Regards Robert McCurdy"Paul Black" wrote in ooglegroups.com... Hi everyone, I have a program that outputs columns of values to a worksheet. I have titles in row 2 and the values start in columns "E3:G?", "I3:L?" etc. I would like to sort the values but I don't know what the last row will be. Here is an example for columns "E3:G?" ... Range("E3:G?").Select Selection.Sort Key1:=Range("G3"), Order1:=xlDescending, Key2:=Range("E3") _ , Order2:=xlAscending, Key3:=Range("F3"), Order3:=xlAscending ... with the ? is the unknown number to sort down to. Thanks in Advance. All the Best. Paul |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Sort Without Knowing Last Cell
"... are you saying that this is NOT the optimum way to do it?. "
No. I didn't say that.. but it isn't. Range("E3:G3").End(xlDown) is only one cell at the bottom of contiguous data in column E, you are probably leaving it up to Excel to guess the range from there. To show this, try this statement and look what's selected: Range("E3:G3").End(xlDown).select -- p45cal "Paul Black" wrote: Thanks for the reply p45cal, I had just worked out doing it this way ... Range("E3:G3").End(xlDown).Sort _ Key1:=Range("G3"), Order1:=xlDescending, _ Key2:=Range("E3"), Order2:=xlAscending, _ Key3:=Range("F3"), Order3:=xlAscending .... and ... Range("I3:L3").End(xlDown).Sort _ Key1:=Range("L3"), Order1:=xlDescending, _ Key2:=Range("I3"), Order2:=xlAscending, _ Key3:=Range("J3"), Order3:=xlAscending .... are you saying that this is NOT the optimum way to do it?. Thanks in Advance. All the Best. Paul On Aug 31, 11:19 am, "Robert McCurdy" wrote: A few questions: Does your table have headers in row 2? Is your table clear of internal blank rows? Are there other tables or data below your desired sortable range/table? Assuming the answers are, Yes, Yes, No (as it should), then: Sub Dsort() Dim SortRng As Range Set SortRng = Range("E2:G" & Rows.Count) SortRng.Sort Key1:=Range("G2"), Order1:=xlDescending, Header:=xlYes, _ Key2:=Range("E2"), Order2:=xlAscending, Header:=xlYes, _ Key3:=Range("F2"), Order3:=xlAscending, Header:=xlYes End Sub Regards Robert McCurdy"Paul Black" wrote in ooglegroups.com... Hi everyone, I have a program that outputs columns of values to a worksheet. I have titles in row 2 and the values start in columns "E3:G?", "I3:L?" etc. I would like to sort the values but I don't know what the last row will be. Here is an example for columns "E3:G?" ... Range("E3:G?").Select Selection.Sort Key1:=Range("G3"), Order1:=xlDescending, Key2:=Range("E3") _ , Order2:=xlAscending, Key3:=Range("F3"), Order3:=xlAscending ... with the ? is the unknown number to sort down to. Thanks in Advance. All the Best. Paul |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Sort Without Knowing Last Cell
Hi p45cal,
The selected cells as per your example for both the examples I gave turned out to be the last cell with data in which is correct. Hi Robert McCurdy, My ranges do have headers in and I have adjusted my code accordingly, thank you. Thanks in Advance. All the Best. Paul On Aug 31, 11:42 am, p45cal wrote: "... are you saying that this is NOT the optimum way to do it?. " No. I didn't say that.. but it isn't. Range("E3:G3").End(xlDown) is only one cell at the bottom of contiguous data in column E, you are probably leaving it up to Excel to guess the range from there. To show this, try this statement and look what's selected: Range("E3:G3").End(xlDown).select -- p45cal "Paul Black" wrote: Thanks for the reply p45cal, I had just worked out doing it this way ... Range("E3:G3").End(xlDown).Sort _ Key1:=Range("G3"), Order1:=xlDescending, _ Key2:=Range("E3"), Order2:=xlAscending, _ Key3:=Range("F3"), Order3:=xlAscending .... and ... Range("I3:L3").End(xlDown).Sort _ Key1:=Range("L3"), Order1:=xlDescending, _ Key2:=Range("I3"), Order2:=xlAscending, _ Key3:=Range("J3"), Order3:=xlAscending .... are you saying that this is NOT the optimum way to do it?. Thanks in Advance. All the Best. Paul On Aug 31, 11:19 am, "Robert McCurdy" wrote: A few questions: Does your table have headers in row 2? Is your table clear of internal blank rows? Are there other tables or data below your desired sortable range/table? Assuming the answers are, Yes, Yes, No (as it should), then: Sub Dsort() Dim SortRng As Range Set SortRng = Range("E2:G" & Rows.Count) SortRng.Sort Key1:=Range("G2"), Order1:=xlDescending, Header:=xlYes, _ Key2:=Range("E2"), Order2:=xlAscending, Header:=xlYes, _ Key3:=Range("F2"), Order3:=xlAscending, Header:=xlYes End Sub Regards Robert McCurdy"Paul Black" wrote in ooglegroups.com... Hi everyone, I have a program that outputs columns of values to a worksheet. I have titles in row 2 and the values start in columns "E3:G?", "I3:L?" etc. I would like to sort the values but I don't know what the last row will be. Here is an example for columns "E3:G?" ... Range("E3:G?").Select Selection.Sort Key1:=Range("G3"), Order1:=xlDescending, Key2:=Range("E3") _ , Order2:=xlAscending, Key3:=Range("F3"), Order3:=xlAscending ... with the ? is the unknown number to sort down to. Thanks in Advance. All the Best. Paul- Hide quoted text - - Show quoted text - |
|
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Sort Without Knowing Last Cell
"turned out to be the last cell with data in which is correct."
It is correct as far as that's what I said they'd be, but it isn't ideal to use that range in Range("E3:G3").End(xlDown).Sort _ ... as you're letting excel guess what range should be sorted. With Robert's code, I think you are sorting the entire column, right to the bottom of the sheet, fine if there's nothing below, ever. But he did check that aspect with you. You close with "Thanks in Advance.", does that mean you're NOT sorted? -- p45cal "Paul Black" wrote: Hi p45cal, The selected cells as per your example for both the examples I gave turned out to be the last cell with data in which is correct. Hi Robert McCurdy, My ranges do have headers in and I have adjusted my code accordingly, thank you. Thanks in Advance. All the Best. Paul |
|
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Sort Without Knowing Last Cell
Hi p45cal,
Nice play on words, I like it. How would I incorporate your code into mine to get the optimum effect please. Do I need to set a variable up or something. Thanks in Advance. All the Best. Paul On Aug 31, 1:40 pm, p45cal wrote: "turned out to be the last cell with data in which is correct." It is correct as far as that's what I said they'd be, but it isn't ideal to use that range in Range("E3:G3").End(xlDown).Sort _ ... as you're letting excel guess what range should be sorted. With Robert's code, I think you are sorting the entire column, right to the bottom of the sheet, fine if there's nothing below, ever. But he did check that aspect with you. You close with "Thanks in Advance.", does that mean you're NOT sorted? -- p45cal "Paul Black" wrote: Hi p45cal, The selected cells as per your example for both the examples I gave turned out to be the last cell with data in which is correct. Hi Robert McCurdy, My ranges do have headers in and I have adjusted my code accordingly, thank you. Thanks in Advance. All the Best. Paul- Hide quoted text - - Show quoted text - |
|
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Sort Without Knowing Last Cell
Your original snippet:
Range("E3:G?").Select Selection.Sort Key1:=Range("G3"), Order1:=xlDescending, Key2:=Range("E3") _ , Order2:=xlAscending, Key3:=Range("F3"), Order3:=xlAscending My bits incorporated (a novel name for a company perhaps?): If you know that column G will always be fully populated right to the bottom then: lastrow=range("G3").End(xlDown).Row Range("E3:G" & lastrow).Sort Key1:=Range("G3"), Order1:=xlDescending, Key2:=Range("E3") _ , Order2:=xlAscending, Key3:=Range("F3"), Order3:=xlAscending If it's column E you know to be fully populated then: lastrow=range("E3").End(xlDown).Row Range("E3:G" & lastrow).Sort Key1:=Range("G3"), Order1:=xlDescending, Key2:=Range("E3") _ , Order2:=xlAscending, Key3:=Range("F3"), Order3:=xlAscending If none of the rows is always guaranteed to be fully populated then you might try: lastrow=range("E3").CurrentRegion.Row+range("E3"). CurrentRegion.Rows.Count-1 Range("E3:G" & lastrow).Sort Key1:=Range("G3"), Order1:=xlDescending, Key2:=Range("E3") _ , Order2:=xlAscending, Key3:=Range("F3"), Order3:=xlAscending -- p45cal "Paul Black" wrote: Hi p45cal, Nice play on words, I like it. How would I incorporate your code into mine to get the optimum effect please. Do I need to set a variable up or something. Thanks in Advance. All the Best. Paul On Aug 31, 1:40 pm, p45cal wrote: "turned out to be the last cell with data in which is correct." It is correct as far as that's what I said they'd be, but it isn't ideal to use that range in Range("E3:G3").End(xlDown).Sort _ ... as you're letting excel guess what range should be sorted. With Robert's code, I think you are sorting the entire column, right to the bottom of the sheet, fine if there's nothing below, ever. But he did check that aspect with you. You close with "Thanks in Advance.", does that mean you're NOT sorted? -- p45cal "Paul Black" wrote: Hi p45cal, The selected cells as per your example for both the examples I gave turned out to be the last cell with data in which is correct. Hi Robert McCurdy, My ranges do have headers in and I have adjusted my code accordingly, thank you. Thanks in Advance. All the Best. Paul- Hide quoted text - - Show quoted text - |
|
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Sort Without Knowing Last Cell
maybe another way for you to try:
Sub Real_lastrow() Dim LastRow1 As Long Dim ws As Worksheet Dim arr As Variant Dim i As Long, z As Long Set ws = Worksheets("Sheet1") z = 1 ReDim arr(1) For i = 5 To 7 ' columns E to G ReDim Preserve arr(1 To z) arr(z) = ws.Cells(Rows.Count, i).End(xlUp).Row z = z + 1 Next LastRow1 = Application.Max(arr) With ws.Range("E3:G" & LastRow1) .Sort Key1:=ws.Range("G3"), Order1:=xlDescending, Key2:=ws.Range("E3") _ , Order2:=xlAscending, Key3:=ws.Range("F3"), Order3:=xlAscending End With End Sub -- Gary "Paul Black" wrote in message oups.com... Hi everyone, I have a program that outputs columns of values to a worksheet. I have titles in row 2 and the values start in columns "E3:G?", "I3:L?" etc. I would like to sort the values but I don't know what the last row will be. Here is an example for columns "E3:G?" ... Range("E3:G?").Select Selection.Sort Key1:=Range("G3"), Order1:=xlDescending, Key2:=Range("E3") _ , Order2:=xlAscending, Key3:=Range("F3"), Order3:=xlAscending ... with the ? is the unknown number to sort down to. Thanks in Advance. All the Best. Paul |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Knowing the number | Excel Discussion (Misc queries) | |||
| Identifying value in a cell knowing R1C1 naming | Excel Programming | |||
| Knowing when Solver closes | Excel Programming | |||
| getting a y-value from a graph knowing the corresponding x-value | Excel Discussion (Misc queries) | |||
| Select from first worksheet without knowing it's name | Excel Programming | |||
Linear Mode
