Setting a sum formula
 

I have the following code:

Set EndRow = TargetSheet.Range("A1", TargetSheet.Range("A65536").End
(xlUp).Address)
ListRow = EndRow.Row + EndRow.Rows.Count

What I am trying to do is determine the last row of data on a sheet, go to
the cell right below it, and sum up everything above it. The number of rows
will always be different. Please help. Thanks.

Setting a sum formula
 

Hi

That's what R1C1 does. This will sum "everything above it" just anywhere as
long as it's not in row 1:

ActiveCell.FormulaR1C1 = "=SUM(R1C1:R[-1]C)"

replace "Activecell" with whatever addressing system you use. Do not
activate.

HTH. Best wishes Harald

"Mike" skrev i melding
...
I have the following code:

Set EndRow = TargetSheet.Range("A1", TargetSheet.Range("A65536").End
(xlUp).Address)
ListRow = EndRow.Row + EndRow.Rows.Count

What I am trying to do is determine the last row of data on a sheet, go to
the cell right below it, and sum up everything above it. The number of
rows
will always be different. Please help. Thanks.

Setting a sum formula
 

Ooops. Sorry, typo. Should read:

ActiveCell.FormulaR1C1 = "=SUM(R1C:R[-1]C)"


"Harald Staff" skrev i melding
...
Hi

That's what R1C1 does. This will sum "everything above it" just anywhere
as
long as it's not in row 1:

ActiveCell.FormulaR1C1 = "=SUM(R1C1:R[-1]C)"

replace "Activecell" with whatever addressing system you use. Do not
activate.

HTH. Best wishes Harald

"Mike" skrev i melding
...
I have the following code:

Set EndRow = TargetSheet.Range("A1", TargetSheet.Range("A65536").End
(xlUp).Address)
ListRow = EndRow.Row + EndRow.Rows.Count

What I am trying to do is determine the last row of data on a sheet, go
to
the cell right below it, and sum up everything above it. The number of
rows
will always be different. Please help. Thanks.

Setting a sum formula
 

Try this

EndRow = TargetSheet.Range("A" & Rows.Count).End(xlUp).Row
If EndRow 1 Or Range("A1").Value < "" Then
Cels(EndRow+1, "A").Formula = "=SUM(A1:A" & EndCell.Row & ")"
End If

--

HTH

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


"Mike" wrote in message
...
I have the following code:

Set EndRow = TargetSheet.Range("A1", TargetSheet.Range("A65536").End
(xlUp).Address)
ListRow = EndRow.Row + EndRow.Rows.Count

What I am trying to do is determine the last row of data on a sheet, go to
the cell right below it, and sum up everything above it. The number of
rows
will always be different. Please help. Thanks.