software

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!!!

does this help?

lastrow=cells(rows.count,quot;aquot;).end(xlup).row

--
Don Guillett
SalesAid Software

quot;bodhisatvaofboogiequot; gt; wrote in
message ...
gt;I am not sure how to classify this problem.
gt;
gt; I wish to autosum a column. Usually, clicking on the column at the top to
gt; select the whole thing, then simply clicking the auto sum will place the
gt; subtotal at the bottom of the page. WELL, the bottom of the page happens
gt; to
gt; be on line 65536. My document is only like 100 rows deep, so that makes
gt; for
gt; a lot of scrolling down LOL. SO, my question is:
gt;
gt; How do I auto sum so that the subtotal will just appear in a blank cell
gt; immediately following the last cell in the column with a value? I'm
gt; trying
gt; to put this into a macro, so that if I bring in a document that has 300,
gt; 500,
gt; or any number of lines it will pick a blank cell at the bottom of the
gt; column
gt; with values and not on line 65536... THANKS!!!
Not sure how to write a macro that would take you to quot;totalquot; 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

quot;bodhisatvaofboogiequot; wrote:

gt; I am not sure how to classify this problem.
gt;
gt; I wish to autosum a column. Usually, clicking on the column at the top to
gt; select the whole thing, then simply clicking the auto sum will place the
gt; subtotal at the bottom of the page. WELL, the bottom of the page happens to
gt; be on line 65536. My document is only like 100 rows deep, so that makes for
gt; a lot of scrolling down LOL. SO, my question is:
gt;
gt; How do I auto sum so that the subtotal will just appear in a blank cell
gt; immediately following the last cell in the column with a value? I'm trying
gt; to put this into a macro, so that if I bring in a document that has 300, 500,
gt; or any number of lines it will pick a blank cell at the bottom of the column
gt; with values and not on line 65536... THANKS!!!

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!!!

quot;Don Guillettquot; wrote:

gt; does this help?
gt;
gt; lastrow=cells(rows.count,quot;aquot;).end(xlup).row
gt;
gt; --
gt; Don Guillett
gt; SalesAid Software
gt;
gt; quot;bodhisatvaofboogiequot; gt; wrote in
gt; message ...
gt; gt;I am not sure how to classify this problem.
gt; gt;
gt; gt; I wish to autosum a column. Usually, clicking on the column at the top to
gt; gt; select the whole thing, then simply clicking the auto sum will place the
gt; gt; subtotal at the bottom of the page. WELL, the bottom of the page happens
gt; gt; to
gt; gt; be on line 65536. My document is only like 100 rows deep, so that makes
gt; gt; for
gt; gt; a lot of scrolling down LOL. SO, my question is:
gt; gt;
gt; gt; How do I auto sum so that the subtotal will just appear in a blank cell
gt; gt; immediately following the last cell in the column with a value? I'm
gt; gt; trying
gt; gt; to put this into a macro, so that if I bring in a document that has 300,
gt; gt; 500,
gt; gt; or any number of lines it will pick a blank cell at the bottom of the
gt; gt; column
gt; gt; with values and not on line 65536... THANKS!!!
gt;
gt;
gt;

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 quot;endquot; quot;down arrowquot;
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

quot;SteveJquot; wrote:

gt; Not sure how to write a macro that would take you to quot;totalquot; line below the
gt; last cell of data. An option I use (to get to any/next cell with data) is to
gt; use the 'arrow key' while holding down 'ctrl.' For instance, if you're in
gt; cell A1 and press 'ctrl' and the down arrow once, you will be taken to the
gt; next blank cell. So if you on say row 1500 and use the above action, you
gt; would be taken to the last row that has data, by pressing the arrow key
gt; again, it would take you to the very bottom of the sheet. Same theory works
gt; with all arrow keys. Hope this option helped. SteveJ
gt;
gt; quot;bodhisatvaofboogiequot; wrote:
gt;
gt; gt; I am not sure how to classify this problem.
gt; gt;
gt; gt; I wish to autosum a column. Usually, clicking on the column at the top to
gt; gt; select the whole thing, then simply clicking the auto sum will place the
gt; gt; subtotal at the bottom of the page. WELL, the bottom of the page happens to
gt; gt; be on line 65536. My document is only like 100 rows deep, so that makes for
gt; gt; a lot of scrolling down LOL. SO, my question is:
gt; gt;
gt; gt; How do I auto sum so that the subtotal will just appear in a blank cell
gt; gt; immediately following the last cell in the column with a value? I'm trying
gt; gt; to put this into a macro, so that if I bring in a document that has 300, 500,
gt; gt; or any number of lines it will pick a blank cell at the bottom of the column
gt; gt; with values and not on line 65536... THANKS!!!

You should have posted YOUR efforts for comments but here.

Sub sumatlastrowincolD()
lastrow = Cells(Rows.Count, quot;dquot;).End(xlUp).Row
Cells(lastrow 1, quot;dquot;) = Application.Sum _
(Range(Cells(1, quot;dquot;), Cells(lastrow, quot;dquot;)))
End Sub

--
Don Guillett
SalesAid Software

quot;bodhisatvaofboogiequot; gt; wrote in
message ...
gt; still very new to the macro editing. Just so I don't put it in the wrong
gt; place, where should that be plugged in and how? In simple terms, what
gt; would
gt; the vbe code look like? THANKS!!!
gt;
gt; quot;Don Guillettquot; wrote:
gt;
gt;gt; does this help?
gt;gt;
gt;gt; lastrow=cells(rows.count,quot;aquot;).end(xlup).row
gt;gt;
gt;gt; --
gt;gt; Don Guillett
gt;gt; SalesAid Software
gt;gt;
gt;gt; quot;bodhisatvaofboogiequot; gt; wrote
gt;gt; in
gt;gt; message ...
gt;gt; gt;I am not sure how to classify this problem.
gt;gt; gt;
gt;gt; gt; I wish to autosum a column. Usually, clicking on the column at the top
gt;gt; gt; to
gt;gt; gt; select the whole thing, then simply clicking the auto sum will place
gt;gt; gt; the
gt;gt; gt; subtotal at the bottom of the page. WELL, the bottom of the page
gt;gt; gt; happens
gt;gt; gt; to
gt;gt; gt; be on line 65536. My document is only like 100 rows deep, so that
gt;gt; gt; makes
gt;gt; gt; for
gt;gt; gt; a lot of scrolling down LOL. SO, my question is:
gt;gt; gt;
gt;gt; gt; How do I auto sum so that the subtotal will just appear in a blank cell
gt;gt; gt; immediately following the last cell in the column with a value? I'm
gt;gt; gt; trying
gt;gt; gt; to put this into a macro, so that if I bring in a document that has
gt;gt; gt; 300,
gt;gt; gt; 500,
gt;gt; gt; or any number of lines it will pick a blank cell at the bottom of the
gt;gt; gt; column
gt;gt; gt; with values and not on line 65536... THANKS!!!
gt;gt;
gt;gt;
gt;gt;
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.
quot;Don Guillettquot; wrote:

gt; You should have posted YOUR efforts for comments but here.
gt;
gt; Sub sumatlastrowincolD()
gt; lastrow = Cells(Rows.Count, quot;dquot;).End(xlUp).Row
gt; Cells(lastrow 1, quot;dquot;) = Application.Sum _
gt; (Range(Cells(1, quot;dquot;), Cells(lastrow, quot;dquot;)))
gt; End Sub
gt;
gt; --
gt; Don Guillett
gt; SalesAid Software
gt;
gt; quot;bodhisatvaofboogiequot; gt; wrote in
gt; message ...
gt; gt; still very new to the macro editing. Just so I don't put it in the wrong
gt; gt; place, where should that be plugged in and how? In simple terms, what
gt; gt; would
gt; gt; the vbe code look like? THANKS!!!
gt; gt;
gt; gt; quot;Don Guillettquot; wrote:
gt; gt;
gt; gt;gt; does this help?
gt; gt;gt;
gt; gt;gt; lastrow=cells(rows.count,quot;aquot;).end(xlup).row
gt; gt;gt;
gt; gt;gt; --
gt; gt;gt; Don Guillett
gt; gt;gt; SalesAid Software
gt; gt;gt;
gt; gt;gt; quot;bodhisatvaofboogiequot; gt; wrote
gt; gt;gt; in
gt; gt;gt; message ...
gt; gt;gt; gt;I am not sure how to classify this problem.
gt; gt;gt; gt;
gt; gt;gt; gt; I wish to autosum a column. Usually, clicking on the column at the top
gt; gt;gt; gt; to
gt; gt;gt; gt; select the whole thing, then simply clicking the auto sum will place
gt; gt;gt; gt; the
gt; gt;gt; gt; subtotal at the bottom of the page. WELL, the bottom of the page
gt; gt;gt; gt; happens
gt; gt;gt; gt; to
gt; gt;gt; gt; be on line 65536. My document is only like 100 rows deep, so that
gt; gt;gt; gt; makes
gt; gt;gt; gt; for
gt; gt;gt; gt; a lot of scrolling down LOL. SO, my question is:
gt; gt;gt; gt;
gt; gt;gt; gt; How do I auto sum so that the subtotal will just appear in a blank cell
gt; gt;gt; gt; immediately following the last cell in the column with a value? I'm
gt; gt;gt; gt; trying
gt; gt;gt; gt; to put this into a macro, so that if I bring in a document that has
gt; gt;gt; gt; 300,
gt; gt;gt; gt; 500,
gt; gt;gt; gt; or any number of lines it will pick a blank cell at the bottom of the
gt; gt;gt; gt; column
gt; gt;gt; gt; with values and not on line 65536... THANKS!!!
gt; gt;gt;
gt; gt;gt;
gt; gt;gt;
gt;
gt;
gt;


SteveJquot; wrote:

gt; Not sure how to write a macro that would take you to quot;totalquot; line
below the
gt; last cell of data. An option I use (to get to any/next cell with
data) is to
gt; use the 'arrow key' while holding down 'ctrl.' For instance, if
you're in
gt; cell A1 and press 'ctrl' and the down arrow once, you will be taken
to the
gt; next blank cell. So if you on say row 1500 and use the above action,
you
gt; would be taken to the last row that has data, by pressing the arrow
key
gt; again, it would take you to the very bottom of the sheet. Same theory
works
gt; with all arrow keys. Hope this option helped. SteveJ
gt;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--
LTUser54
------------------------------------------------------------------------
LTUser54's Profile: www.excelforum.com/member.php...oamp;userid=33459
View this thread: www.excelforum.com/showthread...hreadid=543276glad to help

--
Don Guillett
SalesAid Software

quot;bodhisatvaofboogiequot; gt; wrote in
message ...
gt; Wonderful, I was WAYYYY off. LOL Thanks for the help, all of you are
gt; great. I'm sure I'll be back with questions in my novice ways. HUGE
gt; help, really.
gt;
gt;
gt;
gt; quot;Don Guillettquot; wrote:
gt;
gt;gt; You should have posted YOUR efforts for comments but here.
gt;gt;
gt;gt; Sub sumatlastrowincolD()
gt;gt; lastrow = Cells(Rows.Count, quot;dquot;).End(xlUp).Row
gt;gt; Cells(lastrow 1, quot;dquot;) = Application.Sum _
gt;gt; (Range(Cells(1, quot;dquot;), Cells(lastrow, quot;dquot;)))
gt;gt; End Sub
gt;gt;
gt;gt; --
gt;gt; Don Guillett
gt;gt; SalesAid Software
gt;gt;
gt;gt; quot;bodhisatvaofboogiequot; gt; wrote
gt;gt; in
gt;gt; message ...
gt;gt; gt; still very new to the macro editing. Just so I don't put it in the
gt;gt; gt; wrong
gt;gt; gt; place, where should that be plugged in and how? In simple terms, what
gt;gt; gt; would
gt;gt; gt; the vbe code look like? THANKS!!!
gt;gt; gt;
gt;gt; gt; quot;Don Guillettquot; wrote:
gt;gt; gt;
gt;gt; gt;gt; does this help?
gt;gt; gt;gt;
gt;gt; gt;gt; lastrow=cells(rows.count,quot;aquot;).end(xlup).row
gt;gt; gt;gt;
gt;gt; gt;gt; --
gt;gt; gt;gt; Don Guillett
gt;gt; gt;gt; SalesAid Software
gt;gt; gt;gt;
gt;gt; gt;gt; quot;bodhisatvaofboogiequot; gt;
gt;gt; gt;gt; wrote
gt;gt; gt;gt; in
gt;gt; gt;gt; message ...
gt;gt; gt;gt; gt;I am not sure how to classify this problem.
gt;gt; gt;gt; gt;
gt;gt; gt;gt; gt; I wish to autosum a column. Usually, clicking on the column at the
gt;gt; gt;gt; gt; top
gt;gt; gt;gt; gt; to
gt;gt; gt;gt; gt; select the whole thing, then simply clicking the auto sum will place
gt;gt; gt;gt; gt; the
gt;gt; gt;gt; gt; subtotal at the bottom of the page. WELL, the bottom of the page
gt;gt; gt;gt; gt; happens
gt;gt; gt;gt; gt; to
gt;gt; gt;gt; gt; be on line 65536. My document is only like 100 rows deep, so that
gt;gt; gt;gt; gt; makes
gt;gt; gt;gt; gt; for
gt;gt; gt;gt; gt; a lot of scrolling down LOL. SO, my question is:
gt;gt; gt;gt; gt;
gt;gt; gt;gt; gt; How do I auto sum so that the subtotal will just appear in a blank
gt;gt; gt;gt; gt; cell
gt;gt; gt;gt; gt; immediately following the last cell in the column with a value?
gt;gt; gt;gt; gt; I'm
gt;gt; gt;gt; gt; trying
gt;gt; gt;gt; gt; to put this into a macro, so that if I bring in a document that has
gt;gt; gt;gt; gt; 300,
gt;gt; gt;gt; gt; 500,
gt;gt; gt;gt; gt; or any number of lines it will pick a blank cell at the bottom of
gt;gt; gt;gt; gt; the
gt;gt; gt;gt; gt; column
gt;gt; gt;gt; gt; with values and not on line 65536... THANKS!!!
gt;gt; gt;gt;
gt;gt; gt;gt;
gt;gt; gt;gt;
gt;gt;
gt;gt;
gt;gt;