Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Last row + rank help
I am running Excel 2003 and I am trying figure out
how I can find the last row of data in one sheet and use that range to calculate rank on a separate sheet. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Last row + rank help
lr = Cells.Find("*", Cells(Rows.Count, Columns.Count) _ , , , xlByRows, xlPrevious).Row -- Don Guillett Microsoft MVP Excel SalesAid Software "Opal" wrote in message ... I am running Excel 2003 and I am trying figure out how I can find the last row of data in one sheet and use that range to calculate rank on a separate sheet. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Last row + rank help
Hi Opal, Don showed how to get the last row, but I am curious about the
"Calculate Rank". That is a term I am not familiar with. Could you elaborate? "Opal" wrote in message ... I am running Excel 2003 and I am trying figure out how I can find the last row of data in one sheet and use that range to calculate rank on a separate sheet. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Last row + rank help
Thank you Don....
Is this a function that I put into the VBA project? How do I call it on the work sheet. What I mean by rank, is I need to find the last row of data on the sheet (once a week) and rank the values in the range as these will change week by week - I update weekly via pivot table. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Last row + rank help
On Feb 9, 10:50*am, Opal wrote:
Thank you Don.... Is this a function that I put into the VBA project? How do I call it on the work sheet. What I mean by rank, is I need to find the last row of data on the sheet (once a week) and rank the values in the range as these will change week by week - I update weekly via pivot table. Sounds like you're new to macros so I'll step thru this from the very beginning. From your worksheet, press Alt+11 to bring up the macro/vba editor then cut/paste everything below my dashed line there. To run it, press alt+F from your worksheet and choose to run MacroTryThis. Rather than risk messing up your data, it first copies your first worksheet to a new tab named Ranked Values, then selects of all of the cells, sorting ("ranking") column B from smallest to largest ("ascending"). --------------------------------------------------------- Sub MacroTryThis() 'delete the "Ranked Values" worksheet if it already exists Application.DisplayAlerts = False On Error Resume Next Worksheets("Ranked Values").Delete Application.DisplayAlerts = True On Error GoTo 0 'copy sheet1 to a new tab then name it Ranked Values Sheets("Sheet1").Select Sheets("Sheet1").Copy After:=Sheets(1) ActiveSheet.Name = "Ranked Values" 'sort ("rank") column B from smallest to largest Cells.Sort Key1:=Range("B2"), Order1:=xlAscending, Header:=xlGuess, _ OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _ DataOption1:=xlSortNormal 'end with the cursor at cell B2 Range("B2").Select End Sub |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Last row + rank help
But I need to rank a row of data
B2 to B....... Your example looks like it will rank a column not a row.... am I mistaken? |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Last row + rank help
On Feb 9, 3:07*pm, Opal wrote:
But I need to rank a row of data B2 to B....... Your example looks like it will rank a column not a row.... am I mistaken? Correct, data is typically arranged vertically in columns (ie B2:B#). for example: column A column B Date Sales 1-01-2010 14 1-02-2010 5 1-03-2010 15 1-04-2010 22 On the other hand, if your data is arranged horizontally across a row (ie: A6:G6 for instance) then give this a try: for example date Sub MacroTryThis2() 'delete the "Ranked Values" worksheet if it already exists Application.DisplayAlerts = False On Error Resume Next Worksheets("Ranked Values").Delete Application.DisplayAlerts = True On Error GoTo 0 'copy sheet1 to a new tab then name it Ranked Values Sheets("Sheet1").Select Sheets("Sheet1").Copy After:=Sheets(1) ActiveSheet.Name = "Ranked Values" 'presuming Column(A) is your longest one, start at its bottom and look upwards until we find the last row of your data LastRow = Range("A65536").end(xlup).row 'sort ("rank") across the last row of data smallest to largest (ascending) Rows(LastRow).Sort Key1:=Range("A6"), Order1:=xlAscending, Header:=xlGuess, _ OrderCustom:=1, MatchCase:=False, Orientation:=xlLeftToRight, _ DataOption1:=xlSortNormal 'end with the cursor at cell B2 Range("B2").Select End Sub |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Last row + rank help
Hmmmm compile error:
"Function call on left-hand side of assignment must return Variant or Object" BTW, data has been arranged in this manner: Weekof Part A Part B Part C 01/04-01/09 6.7 5.4 3.2 01/11-01/16 7.1 4.9 5.1 01/18-01-23 6.3 6.1 8.2 I need to rank the data week by week If I switch it to the way you first suggested I will eventually run out of columns as the part numbers range from 11 to 28 depending on the production line but the weeks in a year or years will continue to grow. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
rank the numbers / range of data using 'RANK' and 'ABS' | Excel Worksheet Functions | |||
RANK, duplicate ranking but no gaps in rank | Excel Worksheet Functions | |||
Rank where lowest value is highest rank | Excel Worksheet Functions | |||
Does Correl/Rank combo work eg CORREL(cols E & H) where E&H=RANK(. | Excel Worksheet Functions | |||
Rank items, select one start date, have remaining dates follow based on rank | Excel Discussion (Misc queries) |