Last row + rank help

[Opal]

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.

 

[Don Guillett]

lr = Cells.Find("*", Cells(Rows.Count, Columns.Count) _
, , , xlByRows, xlPrevious).Row

 

[JLGWhiz]

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]

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.

 

[Jef Gorbach]

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

 

[Opal]

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?

 

[Jef Gorbach]

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

 

[Opal]

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.