Dim lastrow as Long, firstrow as Long
Dim rng as Range
lastrow = cells(rows.count,"B").End(xlup).row
firstrow = cells(lastrow,"B").End(xlup).row

Set rng = Range(" B" & Firstrow & ":B" & LastRow)
Cells(lastrow + 2,"B").Formula = "=Max(" & rng.Address & ")"

rng.CurrentRegion.Select
application.Dialogs(xlDialogSort).Show

Excel can sort on up to 3 columns at one time - so this just shows the sort
dialog.

--
Regards,
Tom Ogilvy




"rjr" wrote in message
news:QoPjh.5351$_X.2614@bigfe9...
Hello to all and Merry Christmas,



I am responsible for downloading about 20 data sheets daily from the
mainframe and they are saved in excel worksheet format on my desktop
computer.

Each download has a different amount of rows in it. Example: 1 may have 15
rows and 2 may have 200 rows, and 3 may have 25 rows, etc...........



1. My first problem is: How do I make excel recognize the very last row ?

2. If row 200 is the last entry, then I would want the total's added to
row 202, leaving one space between the report and the totals.

3. The totals row would place my formula for each column in this row.
i.e.: one formula would be to count the highest day out.. Column B would
return 7 in the example.

I also may have other formulas that might need to be entered so if
someone wouldn't mind, show me an example of putting a different formula
in the added formula for col B.

4. Then I need to sort the columns by one, two or even three columns. Is
there a way to program a dialog box to pop up, after entering the
formula's and have it ask which columns should be sorted first, second and
third; or would it be simpler to simply ask what col for first sort, then
have another popup ask for 2nd sort and then a 3rd.......... I have little
to no experience here and can only follow directions...



Here's a small example:

A = Source ID - I would like to sort all the data by this column as soon
as all the data is selected.

B = Aging report - Formula will show the highest date for this column

C = names - no need to group

D = Date - no need to sort

E = A special Code and may do the sort by it instead of A -







Example: let's say I have 32 rows of data when I export this document.
When I run the macro I would like it to determine how many rows I have,
add the formula for whatever I need in row 33 and ask me to sort by (give
me the option of putting in which column) and then ask if there is
secondary sort. Once that's completed, it should sort again.



A B C D E

ASTFED 7 Name Date 38fjmv7

978DJDE 6 Name Date dkie8fj8

Etc.....





And finally I have a need, with this same worksheet, to subtotal numbers
by either col A or col E or another column to be determined later. Any
ideas??



Any help would be greatly appreciated. Thanks so much

Bob Reynolds