Auto Sum

[Guest]

I am not sure how to classify this problem.

I wish to autosum a column. Usually, clicking on the column at the top to
select the whole thing, then simply clicking the auto sum will place the
subtotal at the bottom of the page. WELL, the bottom of the page happens to
be on line 65536. My document is only like 100 rows deep, so that makes for
a lot of scrolling down LOL. SO, my question is:

How do I auto sum so that the subtotal will just appear in a blank cell
immediately following the last cell in the column with a value? I'm trying
to put this into a macro, so that if I bring in a document that has 300, 500,
or any number of lines it will pick a blank cell at the bottom of the column
with values and not on line 65536... THANKS!!!

 

[Don Guillett]

does this help?

lastrow=cells(rows.count,"a").end(xlup).row

 

[Guest]

Not sure how to write a macro that would take you to "total" line below the
last cell of data. An option I use (to get to any/next cell with data) is to
use the 'arrow key' while holding down 'ctrl.' For instance, if you're in
cell A1 and press 'ctrl' and the down arrow once, you will be taken to the
next blank cell. So if you on say row 1500 and use the above action, you
would be taken to the last row that has data, by pressing the arrow key
again, it would take you to the very bottom of the sheet. Same theory works
with all arrow keys. Hope this option helped. SteveJ

 

[Guest]

still very new to the macro editing. Just so I don't put it in the wrong
place, where should that be plugged in and how? In simple terms, what would
the vbe code look like? THANKS!!!

 

[Guest]

That is a neat little short cut for moving around, thanks for that. HOWEVER,
as far as creating the macro is concerned, that won't work. I've been
playing around with a macro that is all but finishing correctly. I used a
similar key function to get to the bottom of the colum "end" "down arrow"
takes ya to the bottom. When I put the auto sum in that way, the macro writes
it as changing cell XX in row YY, so if I go to import data that is twice as
long as the previous, it places the auto sum in cell XX in row YY again.
Which is not the bottom of the new data set. LOL

 

[Don Guillett]

You should have posted YOUR efforts for comments but here.

Sub sumatlastrowincolD()
lastrow = Cells(Rows.Count, "d").End(xlUp).Row
Cells(lastrow + 1, "d") = Application.Sum _
(Range(Cells(1, "d"), Cells(lastrow, "d")))
End Sub

 

[Guest]

Wonderful, I was WAYYYY off. LOL Thanks for the help, all of you are
great. I'm sure I'll be back with questions in my novice ways. HUGE
help, really.

 

[LTUser54]

Not sure how to write a macro that would take you to "total" line below the
last cell of data. An option I use (to get to any/next cell with data) is to
use the 'arrow key' while holding down 'ctrl.' For instance, if you're in
cell A1 and press 'ctrl' and the down arrow once, you will be taken to the
next blank cell. So if you on say row 1500 and use the above action, you
would be taken to the last row that has data, by pressing the arrow key
again, it would take you to the very bottom of the sheet. Same theory works
with all arrow keys. Hope this option helped. SteveJ

Steve,

Thanks for the down arrow tip. I had forgotten about that. Just as a
quick reminder, make sure your NumLock key is off it won't work.

Thanks.

Mark

 

[Don Guillett]

glad to help