Calculation based on last cell in a row +1

[Gingertrees]

column A has dates, column B has values.
so, e.g.
A B
8/1 200
8/2 123
8/3 64
8/4 211
8/5 187
8/6 46

etc
It's long. I want excel to find the last row, and show a sum of the values one row above that row.

So if 8/6 is the last row of used data, I want a sum of the B values from 8/1 - 8/5 (omitting bottom row from sum).

I can find the last row with
Lastdata1 = Range("B65536").End(xlUp).Row

But I don't know how to get that into something like
Sum (B2:2ndtolastrow).

And this should be like a workbook_open or worksheet_change or something, as the numbers in the bottom row change constantly.
(Banging head repeatedly as I try and fail to write the correct syntax).

 

[Lewiy]

Lastdata1 = Range("B65536").End(xlUp).Row-1
would give you the row above the last row

 

[VoG]

Try like this

Sub Test()
Dim LR As Long
LR = Range("B" & Rows.Count).End(xlUp).Row
Range("C2").Formula = "=SUM(B2:B" & LR - 1 & ")"
End Sub

 

[Gingertrees]

Thanks, but now I have a new problem.
I placed this in the worksheet module:

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim LR As Long
    On Error GoTo 0
    LR = Range("B" & Rows.Count).End(xlUp).Row
    Range("H1").Formula = "=SUM(B2:B" & LR - 1 & ")"
End Sub

While it does the correct calculation, it causes a fatal error that throws the "Excel has encountered an error and needs to close" message.

???

 

[VoG]

Try

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim LR As Long
    Application.EnableEvents = False
    LR = Range("B" & Rows.Count).End(xlUp).Row
    Range("H1").Formula = "=SUM(B2:B" & LR - 1 & ")"
    Application.EnableEvents = True
End Sub

 

[Gingertrees]

great! Must remember the enable/disable events...