Having a moving Total

I have a worksheet that is linked to a query. When the query updates
the number of rows can either increase or decrease. At the bottom of
the last two columns I want a total for each column.

How can I make the totals move up and down the sheet so they are always
directly below the last row?

To find the first empty row here is some easy code

Dim rngLastRow As Range

Set rngLastRow = Sheet1.Range("A65535").End(xlUp).Offset(1, 0)
rngLastRow.Select 'This line is optional and assumes you want to select the
row

I assume you can take it from here...

HTH

"Keith" wrote:

> I have a worksheet that is linked to a query. When the query updates
> the number of rows can either increase or decrease. At the bottom of
> the last two columns I want a total for each column.
>
> How can I make the totals move up and down the sheet so they are always
> directly below the last row?
>

Assuming there is only 1 Query Table in that sheet,
You can put below code in that sheet's
Worksheet_Change event procedure:
Everytime the query is update, Wroksheet_Change event
for that sheet is fired and below code will do what you want.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim myValue as Double, c
With Me.QueryTables(1).ResultRange
For Each c In .Columns(.Columns.Count)
If IsNumeric(c.Value) Then
myValue = myValue + c.Value
End If
Next c
Me.Cells(.Cells(.Rows.Count + 1, 1).Row, _
.Cells(1, .Columns.Count).Column).Value = myValue
myValue = 0
For Each c In .Columns(.Columns.Count - 1)
If IsNumeric(c.Value) Then
myValue = myValue + c.Value
End If
Next c
Me.Cells(.Cells(.Rows.Count + 1, 1).Row, _
.Cells(1, .Columns.Count - 1).Column).Value = myValue
End With
End Sub

Sharad

"Keith" <[email protected]> wrote in message
news:[email protected]...
>I have a worksheet that is linked to a query. When the query updates the
>number of rows can either increase or decrease. At the bottom of the last
>two columns I want a total for each column.
>
> How can I make the totals move up and down the sheet so they are always
> directly below the last row?