I have two worksheets in my file (Excel 2000) that
automatically refresh (from a Microsoft Query) when a
particular cell (input criteria) is changed. I would like
to add a total row at the bottom of both sheets that sums
the data in columns F thru U. The number of rows is
variable based on which criteria is selected for the query
results. Ideally, I would like a blank row and then the
total row at the bottom. I would also like this to be
automatically triggered by the changing of my criteria
cell if that is possible with a macro.

Thanks in advance for any advice.
Laura

Hi Laura,

Try the code below. You will need to change the worksheet
name "Sheet1" to the name of your worksheet. (The code
assumes that there are Titles in Row 1, and the Columns
are hard coded F thru U - so if that changes you will need
to adjust those.)

There may be more efficient ways to do this, but I believe
it will produce the results you want.

Hope this helps.

Regards,
James S

Sub NewTotals()
' This assumes that Row 1 contains Titles and
' should not be included in the SUM formula.
Dim wks As Worksheet
Dim rng As Range
Dim rngColumns As Range
Dim rngLast As Range
Dim i As Integer

Set wks = ThisWorkbook.Worksheets("Sheet1")
Set rngColumns = wks.Range("F2:U2")

For Each rng In rngColumns
Set rngLast = _
Cells(Rows.Count, rng.Column).End(xlUp)
' Offset by one row due to Titles in Row 1.
If rngLast.Offset(1, 0).Address < rng.Address Then
rngLast.Offset(2, 0).Formula = _
"=SUM(" & rng.Address & ":" &
rngLast.Address & ")"
rngLast.Offset(2, 0).Calculate
End If
Next rng
End Sub


-----Original Message-----
I have two worksheets in my file (Excel 2000) that
automatically refresh (from a Microsoft Query) when a
particular cell (input criteria) is changed. I would
like
to add a total row at the bottom of both sheets that sums
the data in columns F thru U. The number of rows is
variable based on which criteria is selected for the
query
results. Ideally, I would like a blank row and then the
total row at the bottom. I would also like this to be
automatically triggered by the changing of my criteria
cell if that is possible with a macro.

Thanks in advance for any advice.
Laura