Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
HLOOKUP with variable rows | Excel Discussion (Misc queries) | |||
sum and variable rows | Excel Worksheet Functions | |||
Using Variable in Rows(x:y) function | Excel Programming | |||
macro with variable rows | Excel Programming | |||
Selecting rows with variable | Excel Programming |