Prev: Trouble with Cutting and Inserting a Range
Next: how to detect if an addin is running
From: Opal on 8 Feb 2010 16:07
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.

From: Don Guillett on 8 Feb 2010 16:26

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

--
Don Guillett
Microsoft MVP Excel
SalesAid Software
dguillett(a)gmail.com
"Opal" <tmwelton(a)hotmail.com> wrote in message
news:efea394d-9b5d-4c39-9150-5b1252bbe40b(a)c28g2000vbc.googlegroups.com...
>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.
>

From: JLGWhiz on 8 Feb 2010 17:49
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" <tmwelton(a)hotmail.com> wrote in message
news:efea394d-9b5d-4c39-9150-5b1252bbe40b(a)c28g2000vbc.googlegroups.com...
>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.
>


From: Opal on 9 Feb 2010 10:50
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.
From: Jef Gorbach on 9 Feb 2010 13:06
On Feb 9, 10:50 am, Opal <tmwel...(a)hotmail.com> 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
 |  Next  |  Last
Pages: 1 2
Prev: Trouble with Cutting and Inserting a Range
Next: how to detect if an addin is running