Having a moving Total

[Keith]

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?

 

[Guest]

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

 

[Sharad Naik]

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