Sorry for posting this again, previous posting of it never got a
response from anybody and I need to get it solved.

I have a macro that adds new rows and copies formulas to these new
rows, I need to make my totals update to include all the new rows, but
the number of rows added is not static

This is what i have for trying to update the formulas when new rows are
added:
With Worksheets("Data")
..Cells(.UsedRange.Rows.Count, 8).FormulaR1C1 = "=SUM(H2:" & _
..Cells((.UsedRange.Rows.Count - 1), 8) & ")"

..Cells(.UsedRange.Rows.Count, 9).FormulaR1C1 = "=SUM(I2:" & _
..Cells((.UsedRange.Rows.Count - 1), 9) & ")"

..Cells(.UsedRange.Rows.Count, 10).FormulaR1C1 = "=SUM(J2:" & _
..Cells((.UsedRange.Rows.Count - 1), 10) & ") +" _
& .Cells(.UsedRange.Rows.Count, 6).Value

End With

Obviously, it doesn't work as submitted. I'm pretty sure the problem is
in the formula, specifically, when I try to reference the new range i
want to work with. I have an idea of what the problem is, I just can't
for the life of me figure out how to change it to make it work properly

I would also like to make these formulas instead only display the sum
of the visible rows (on the last formula, i would also like it to only
add the cell in column 6 if all the rows are visible, note there is a
column hidden that will always remain hidden)

Subtotal will not work because i'm not using filters when the rows are
hidden

thanks for the help everybody, you've all been great with every
question i've asked in the past

Try

Dim LastRow As Long
With Worksheets("Data")
LastRow = Range("H65536").End(xlUp).Row
..Cells(LastRow, 8).Formula = "=SUM(H2:H" & _
LastRow - 1 & ")"

..Cells(LastRow, 9).Formula = "=SUM(I2:I" & _
LastRow - 1 & ")"

..Cells(LastRow, 10).Formula = "=SUM(J2:J" & _
LastRow - 1 & ") + " & _
.Cells(LastRow, 6).Value
End With

Note that you would need a formula or something to exist in the cell in
column H where you want to put the updated formula ... otherwise this will
overwrite the bottom row of data. And the last row in column F needs to
have a number in it.

Regards

Trevor


"Matt" wrote in message
oups.com...
Sorry for posting this again, previous posting of it never got a
response from anybody and I need to get it solved.

I have a macro that adds new rows and copies formulas to these new
rows, I need to make my totals update to include all the new rows, but
the number of rows added is not static

This is what i have for trying to update the formulas when new rows are
added:
With Worksheets("Data")
.Cells(.UsedRange.Rows.Count, 8).FormulaR1C1 = "=SUM(H2:" & _
.Cells((.UsedRange.Rows.Count - 1), 8) & ")"

.Cells(.UsedRange.Rows.Count, 9).FormulaR1C1 = "=SUM(I2:" & _
.Cells((.UsedRange.Rows.Count - 1), 9) & ")"

.Cells(.UsedRange.Rows.Count, 10).FormulaR1C1 = "=SUM(J2:" & _
.Cells((.UsedRange.Rows.Count - 1), 10) & ") +" _
& .Cells(.UsedRange.Rows.Count, 6).Value

End With

Obviously, it doesn't work as submitted. I'm pretty sure the problem is
in the formula, specifically, when I try to reference the new range i
want to work with. I have an idea of what the problem is, I just can't
for the life of me figure out how to change it to make it work properly

I would also like to make these formulas instead only display the sum
of the visible rows (on the last formula, i would also like it to only
add the cell in column 6 if all the rows are visible, note there is a
column hidden that will always remain hidden)

Subtotal will not work because i'm not using filters when the rows are
hidden

thanks for the help everybody, you've all been great with every
question i've asked in the past

Thanks, this solves part of the situation nicely

any ideas how to implement a function that only totals the visible
rows?

Trevor Shuttleworth wrote:
Try

Dim LastRow As Long
With Worksheets("Data")
LastRow = Range("H65536").End(xlUp).Row
.Cells(LastRow, 8).Formula = "=SUM(H2:H" & _
LastRow - 1 & ")"

.Cells(LastRow, 9).Formula = "=SUM(I2:I" & _
LastRow - 1 & ")"

.Cells(LastRow, 10).Formula = "=SUM(J2:J" & _
LastRow - 1 & ") + " & _
.Cells(LastRow, 6).Value
End With

Note that you would need a formula or something to exist in the cell in
column H where you want to put the updated formula ... otherwise this will
overwrite the bottom row of data. And the last row in column F needs to
have a number in it.

Regards

Trevor


"Matt" wrote in message
oups.com...
Sorry for posting this again, previous posting of it never got a
response from anybody and I need to get it solved.

I have a macro that adds new rows and copies formulas to these new
rows, I need to make my totals update to include all the new rows, but
the number of rows added is not static

This is what i have for trying to update the formulas when new rows are
added:
With Worksheets("Data")
.Cells(.UsedRange.Rows.Count, 8).FormulaR1C1 = "=SUM(H2:" & _
.Cells((.UsedRange.Rows.Count - 1), 8) & ")"

.Cells(.UsedRange.Rows.Count, 9).FormulaR1C1 = "=SUM(I2:" & _
.Cells((.UsedRange.Rows.Count - 1), 9) & ")"

.Cells(.UsedRange.Rows.Count, 10).FormulaR1C1 = "=SUM(J2:" & _
.Cells((.UsedRange.Rows.Count - 1), 10) & ") +" _
& .Cells(.UsedRange.Rows.Count, 6).Value

End With

Obviously, it doesn't work as submitted. I'm pretty sure the problem is
in the formula, specifically, when I try to reference the new range i
want to work with. I have an idea of what the problem is, I just can't
for the life of me figure out how to change it to make it work properly

I would also like to make these formulas instead only display the sum
of the visible rows (on the last formula, i would also like it to only
add the cell in column 6 if all the rows are visible, note there is a
column hidden that will always remain hidden)

Subtotal will not work because i'm not using filters when the rows are
hidden

thanks for the help everybody, you've all been great with every
question i've asked in the past

Use SUBTOTAL instead of SUM, You'll need to check the help for the variant
that you need


"Matt" wrote in message
oups.com...
Thanks, this solves part of the situation nicely

any ideas how to implement a function that only totals the visible
rows?

Trevor Shuttleworth wrote:
Try

Dim LastRow As Long
With Worksheets("Data")
LastRow = Range("H65536").End(xlUp).Row
.Cells(LastRow, 8).Formula = "=SUM(H2:H" & _
LastRow - 1 & ")"

.Cells(LastRow, 9).Formula = "=SUM(I2:I" & _
LastRow - 1 & ")"

.Cells(LastRow, 10).Formula = "=SUM(J2:J" & _
LastRow - 1 & ") + " & _
.Cells(LastRow, 6).Value
End With

Note that you would need a formula or something to exist in the cell in
column H where you want to put the updated formula ... otherwise this
will
overwrite the bottom row of data. And the last row in column F needs to
have a number in it.

Regards

Trevor


"Matt" wrote in message
oups.com...
Sorry for posting this again, previous posting of it never got a
response from anybody and I need to get it solved.

I have a macro that adds new rows and copies formulas to these new
rows, I need to make my totals update to include all the new rows, but
the number of rows added is not static

This is what i have for trying to update the formulas when new rows are
added:
With Worksheets("Data")
.Cells(.UsedRange.Rows.Count, 8).FormulaR1C1 = "=SUM(H2:" & _
.Cells((.UsedRange.Rows.Count - 1), 8) & ")"

.Cells(.UsedRange.Rows.Count, 9).FormulaR1C1 = "=SUM(I2:" & _
.Cells((.UsedRange.Rows.Count - 1), 9) & ")"

.Cells(.UsedRange.Rows.Count, 10).FormulaR1C1 = "=SUM(J2:" & _
.Cells((.UsedRange.Rows.Count - 1), 10) & ") +" _
& .Cells(.UsedRange.Rows.Count, 6).Value

End With

Obviously, it doesn't work as submitted. I'm pretty sure the problem is
in the formula, specifically, when I try to reference the new range i
want to work with. I have an idea of what the problem is, I just can't
for the life of me figure out how to change it to make it work properly

I would also like to make these formulas instead only display the sum
of the visible rows (on the last formula, i would also like it to only
add the cell in column 6 if all the rows are visible, note there is a
column hidden that will always remain hidden)

Subtotal will not work because i'm not using filters when the rows are
hidden

thanks for the help everybody, you've all been great with every
question i've asked in the past

subtotal doesn't work, i've tried it, its because i'm not using filters
(i've tested it as i thought it would work myself)

if necessary i have a hidden sheet that can store temp values, hoping i
don't have to use this sheet though because i'm trying to remove it
completely

the hidden rows are never going to be the same unfortunately, as i know
how i can handle that

Trevor Shuttleworth wrote:
Use SUBTOTAL instead of SUM, You'll need to check the help for the variant
that you need


"Matt" wrote in message
oups.com...
Thanks, this solves part of the situation nicely

any ideas how to implement a function that only totals the visible
rows?

Trevor Shuttleworth wrote:
Try

Dim LastRow As Long
With Worksheets("Data")
LastRow = Range("H65536").End(xlUp).Row
.Cells(LastRow, 8).Formula = "=SUM(H2:H" & _
LastRow - 1 & ")"

.Cells(LastRow, 9).Formula = "=SUM(I2:I" & _
LastRow - 1 & ")"

.Cells(LastRow, 10).Formula = "=SUM(J2:J" & _
LastRow - 1 & ") + " & _
.Cells(LastRow, 6).Value
End With

Note that you would need a formula or something to exist in the cell in
column H where you want to put the updated formula ... otherwise this
will
overwrite the bottom row of data. And the last row in column F needs to
have a number in it.

Regards

Trevor


"Matt" wrote in message
oups.com...
Sorry for posting this again, previous posting of it never got a
response from anybody and I need to get it solved.

I have a macro that adds new rows and copies formulas to these new
rows, I need to make my totals update to include all the new rows, but
the number of rows added is not static

This is what i have for trying to update the formulas when new rows are
added:
With Worksheets("Data")
.Cells(.UsedRange.Rows.Count, 8).FormulaR1C1 = "=SUM(H2:" & _
.Cells((.UsedRange.Rows.Count - 1), 8) & ")"

.Cells(.UsedRange.Rows.Count, 9).FormulaR1C1 = "=SUM(I2:" & _
.Cells((.UsedRange.Rows.Count - 1), 9) & ")"

.Cells(.UsedRange.Rows.Count, 10).FormulaR1C1 = "=SUM(J2:" & _
.Cells((.UsedRange.Rows.Count - 1), 10) & ") +" _
& .Cells(.UsedRange.Rows.Count, 6).Value

End With

Obviously, it doesn't work as submitted. I'm pretty sure the problem is
in the formula, specifically, when I try to reference the new range i
want to work with. I have an idea of what the problem is, I just can't
for the life of me figure out how to change it to make it work properly

I would also like to make these formulas instead only display the sum
of the visible rows (on the last formula, i would also like it to only
add the cell in column 6 if all the rows are visible, note there is a
column hidden that will always remain hidden)

Subtotal will not work because i'm not using filters when the rows are
hidden

thanks for the help everybody, you've all been great with every
question i've asked in the past

Sorry, assumed you were filtering. You could try this home-grown UDF if it
helps.

Function SumVisible(rRange As Range) As Long
Dim Cell As Range
Application.Volatile
SumVisible = 0
For Each Cell In rRange
If Cell.EntireRow.Hidden = False Then
If IsNumeric(Cell.Value) Then
SumVisible = SumVisible + Cell.Value
End If
End If
Next 'Cell
End Function

There's not much in the way of error handling but it might give you a start.

Regards

Trevor


"Matt" wrote in message
ps.com...
subtotal doesn't work, i've tried it, its because i'm not using filters
(i've tested it as i thought it would work myself)

if necessary i have a hidden sheet that can store temp values, hoping i
don't have to use this sheet though because i'm trying to remove it
completely

the hidden rows are never going to be the same unfortunately, as i know
how i can handle that

Trevor Shuttleworth wrote:
Use SUBTOTAL instead of SUM, You'll need to check the help for the
variant
that you need


"Matt" wrote in message
oups.com...
Thanks, this solves part of the situation nicely

any ideas how to implement a function that only totals the visible
rows?

Trevor Shuttleworth wrote:
Try

Dim LastRow As Long
With Worksheets("Data")
LastRow = Range("H65536").End(xlUp).Row
.Cells(LastRow, 8).Formula = "=SUM(H2:H" & _
LastRow - 1 & ")"

.Cells(LastRow, 9).Formula = "=SUM(I2:I" & _
LastRow - 1 & ")"

.Cells(LastRow, 10).Formula = "=SUM(J2:J" & _
LastRow - 1 & ") + " & _
.Cells(LastRow, 6).Value
End With

Note that you would need a formula or something to exist in the cell
in
column H where you want to put the updated formula ... otherwise this
will
overwrite the bottom row of data. And the last row in column F needs
to
have a number in it.

Regards

Trevor


"Matt" wrote in message
oups.com...
Sorry for posting this again, previous posting of it never got a
response from anybody and I need to get it solved.

I have a macro that adds new rows and copies formulas to these new
rows, I need to make my totals update to include all the new rows,
but
the number of rows added is not static

This is what i have for trying to update the formulas when new rows
are
added:
With Worksheets("Data")
.Cells(.UsedRange.Rows.Count, 8).FormulaR1C1 = "=SUM(H2:" & _
.Cells((.UsedRange.Rows.Count - 1), 8) & ")"

.Cells(.UsedRange.Rows.Count, 9).FormulaR1C1 = "=SUM(I2:" & _
.Cells((.UsedRange.Rows.Count - 1), 9) & ")"

.Cells(.UsedRange.Rows.Count, 10).FormulaR1C1 = "=SUM(J2:" & _
.Cells((.UsedRange.Rows.Count - 1), 10) & ") +" _
& .Cells(.UsedRange.Rows.Count, 6).Value

End With

Obviously, it doesn't work as submitted. I'm pretty sure the problem
is
in the formula, specifically, when I try to reference the new range
i
want to work with. I have an idea of what the problem is, I just
can't
for the life of me figure out how to change it to make it work
properly

I would also like to make these formulas instead only display the
sum
of the visible rows (on the last formula, i would also like it to
only
add the cell in column 6 if all the rows are visible, note there is
a
column hidden that will always remain hidden)

Subtotal will not work because i'm not using filters when the rows
are
hidden

thanks for the help everybody, you've all been great with every
question i've asked in the past