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
|