I undertake a repetitive task, which seems to me is begging to be
turned into a macro. The extent of my knowledge of macros is (a)
start recording, (b) do some stuff, (c) stop recording. For most
purposes this has been just fine. Now I want to do some more complex
things and I've run into a problem.

I have a column of numbers (say, for this example, A2, A3, A4). I
wish to sum the column. I usually make the cell immediately to the
right of the top number active (B2), and use autosum. Autosum is
great, as it highlights the top of my column of numbers, and I can
then use ctrl-shift-downarrow to automatically capture all my data.
What this does is creates this formula: =SUM(A2:A4).

I recorded my macro, doing just that, and it worked fine. But here is
the problem. The number of rows in my data is variable from one day
to the next. The next time I wish to do the calculation, my data
might run from, say, A2, A3, A4, A5. However, the macro retains the
formula =SUM(A2:A4), whereas is should be =SUM(A2:A5).

How do I make the formula dynamic based on the number of rows?

Ron

try
x=cells(rows.count,"a").end(xlup).row
msgbox application.sum(range("a2:a" & x))

--
Don Guillett
SalesAid Software

wrote in message
...

I undertake a repetitive task, which seems to me is begging to be
turned into a macro. The extent of my knowledge of macros is (a)
start recording, (b) do some stuff, (c) stop recording. For most
purposes this has been just fine. Now I want to do some more complex
things and I've run into a problem.

I have a column of numbers (say, for this example, A2, A3, A4). I
wish to sum the column. I usually make the cell immediately to the
right of the top number active (B2), and use autosum. Autosum is
great, as it highlights the top of my column of numbers, and I can
then use ctrl-shift-downarrow to automatically capture all my data.
What this does is creates this formula: =SUM(A2:A4).

I recorded my macro, doing just that, and it worked fine. But here is
the problem. The number of rows in my data is variable from one day
to the next. The next time I wish to do the calculation, my data
might run from, say, A2, A3, A4, A5. However, the macro retains the
formula =SUM(A2:A4), whereas is should be =SUM(A2:A5).

How do I make the formula dynamic based on the number of rows?

Ron

sum(a:a) will sum all the numbers in column A.

"Don Guillett" wrote:

try
x=cells(rows.count,"a").end(xlup).row
msgbox application.sum(range("a2:a" & x))

--
Don Guillett
SalesAid Software

wrote in message
...

I undertake a repetitive task, which seems to me is begging to be
turned into a macro. The extent of my knowledge of macros is (a)
start recording, (b) do some stuff, (c) stop recording. For most
purposes this has been just fine. Now I want to do some more complex
things and I've run into a problem.

I have a column of numbers (say, for this example, A2, A3, A4). I
wish to sum the column. I usually make the cell immediately to the
right of the top number active (B2), and use autosum. Autosum is
great, as it highlights the top of my column of numbers, and I can
then use ctrl-shift-downarrow to automatically capture all my data.
What this does is creates this formula: =SUM(A2:A4).

I recorded my macro, doing just that, and it worked fine. But here is
the problem. The number of rows in my data is variable from one day
to the next. The next time I wish to do the calculation, my data
might run from, say, A2, A3, A4, A5. However, the macro retains the
formula =SUM(A2:A4), whereas is should be =SUM(A2:A5).

How do I make the formula dynamic based on the number of rows?

Ron

OP wanted to start with A2, not a1

--
Don Guillett
SalesAid Software

"Kurt M. Sanger" wrote in message
...
sum(a:a) will sum all the numbers in column A.

"Don Guillett" wrote:

try
x=cells(rows.count,"a").end(xlup).row
msgbox application.sum(range("a2:a" & x))

--
Don Guillett
SalesAid Software

wrote in message
...

I undertake a repetitive task, which seems to me is begging to be
turned into a macro. The extent of my knowledge of macros is (a)
start recording, (b) do some stuff, (c) stop recording. For most
purposes this has been just fine. Now I want to do some more complex
things and I've run into a problem.

I have a column of numbers (say, for this example, A2, A3, A4). I
wish to sum the column. I usually make the cell immediately to the
right of the top number active (B2), and use autosum. Autosum is
great, as it highlights the top of my column of numbers, and I can
then use ctrl-shift-downarrow to automatically capture all my data.
What this does is creates this formula: =SUM(A2:A4).

I recorded my macro, doing just that, and it worked fine. But here is
the problem. The number of rows in my data is variable from one day
to the next. The next time I wish to do the calculation, my data
might run from, say, A2, A3, A4, A5. However, the macro retains the
formula =SUM(A2:A4), whereas is should be =SUM(A2:A5).

How do I make the formula dynamic based on the number of rows?

Ron