sum variable # of rows
 

I am writing some code to clean up data I imported into excel. I will have a
vraiable number of rows each time I run the macro.

Always in Coulmn B is the number I want to sum. What function can I use to
count the number of rows I have & then put the sum of Column B in the next
free row. i.e.

Rows 1-5 have data , put sum of B1:B5 into B6 where next time it could be
B1:B10 sum in B11.

Thank you.
Regard,
Diane

sum variable # of rows
 

Hi,
Try this:

Dim lRow As Long

Const Formula As String = "=sum(b1:bX)"

lRow = Cells(Rows.Count, "B").End(xlUp).Row ' find last row in colum b

Cells(lRow + 1, "B") = Application.Substitute(Formula, "X", lRow) '
substitute X by lrow in SUM formula


HTH
"Diane" wrote:

I am writing some code to clean up data I imported into excel. I will have a
vraiable number of rows each time I run the macro.

Always in Coulmn B is the number I want to sum. What function can I use to
count the number of rows I have & then put the sum of Column B in the next
free row. i.e.

Rows 1-5 have data , put sum of B1:B5 into B6 where next time it could be
B1:B10 sum in B11.

Thank you.
Regard,
Diane

sum variable # of rows
 

The following puts the formula into the last cell in column B + 1

Cells(Cells(65336, "B").End(xlUp).Row + 1, 2) = "=SUM(B1:B" & Cells(65536,
"B").End(xlUp).Row & ")"


--
Cheers
Nigel



"Diane" wrote in message
...
I am writing some code to clean up data I imported into excel. I will have
a
vraiable number of rows each time I run the macro.

Always in Coulmn B is the number I want to sum. What function can I use
to
count the number of rows I have & then put the sum of Column B in the next
free row. i.e.

Rows 1-5 have data , put sum of B1:B5 into B6 where next time it could be
B1:B10 sum in B11.

Thank you.
Regard,
Diane

sum variable # of rows
 

As you ask in programming, I assume you want VBA

iLastRow = Cells(Rows.Count,"B").End(xlUp).Row
Cell(iLastRow+1,"B").Formula = "SUM(B1:B" & iLastRow & ")"

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Diane" wrote in message
...
I am writing some code to clean up data I imported into excel. I will have
a
vraiable number of rows each time I run the macro.

Always in Coulmn B is the number I want to sum. What function can I use
to
count the number of rows I have & then put the sum of Column B in the next
free row. i.e.

Rows 1-5 have data , put sum of B1:B5 into B6 where next time it could be
B1:B10 sum in B11.

Thank you.
Regard,
Diane

sum variable # of rows
 

One Mo

cells(rows.count,"B").End(xlup)(2).FormulaR1C1 = "=Sum(R1C:R[-1]C)"

--
Regards,
Tom Ogilvy


"Diane" wrote in message
...
I am writing some code to clean up data I imported into excel. I will have
a
vraiable number of rows each time I run the macro.

Always in Coulmn B is the number I want to sum. What function can I use
to
count the number of rows I have & then put the sum of Column B in the next
free row. i.e.

Rows 1-5 have data , put sum of B1:B5 into B6 where next time it could be
B1:B10 sum in B11.

Thank you.
Regard,
Diane