cells(1,"IV").End(xltoLeft).Column

use a row that will give you the acurate count.

--
Regards,
Tom Ogilvy

"rjr" wrote in message
. ..
Tom,

forget my other post about the maxa, since I hadn't completed looking
through your response.

The MaxA works fine and is doing exactly what I wanted it to. I tried the
Ctrl-Shift-8 and it did what you said it would do and confirmed I had what
I wanted selected....

Is there something I could put in there to automatically calculate the
number of columns... rng.offset(0,-1).Resize(,20) make the 20 where it
would calculate it????


"Tom Ogilvy" wrote in message
...
rng refers to column B, so you would offset it one column as shown below.

Cells(lastrow + 2,"c").Formula = "=Maxa(" & rng.Offset(0,1).Address & ")"

If you want the sum, then Use Sum instead of Maxa.

Current region should select all the columns that are contiguous. Click
in your data and do Ctrl+Shift+8

this is the equivalent of the current region command. If that picks up
all the data you want, then you shouldn't need to change. If it doesn't,
then you might need to alter the code

Assuming rng refers to the proper number of rows in column B, then

rng.offset(0,-1).Resize(,20)

would pick up columns A (the offset -1) for 20 columns as an example.


--
Regards,
Tom Ogilvy



"rjr" wrote in message
...
Tom Thank you, as usual I have another one or two questions.
1. If I have varing columns? some times I'll have 8, 10, 15 etc so they
will vary for me. Do I need to set them?

2. Set rng = Range(" B" & Firstrow & ":B" & LastRow)
Cells(lastrow + 2,"c").Formula = "=Maxa(" & rng.Address & ")" I
modified this to give me totals for column "C" with the formula maxa
().. Obviously it didn't work as it returned a zero, what do i need to
change in your formula. I thought it would auto update the column
automatically??
Thanks for your patience
Bob






"Tom Ogilvy" wrote in message
...

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