Get a total when number or rows is not know in advance
 

Not sure which colum your needing to sum but this may work
With Worksheets("Sheet1")
If IsEmpty(.Cells(.Rows.Count, 2)) Then
With .Cells(.Rows.Count, 2).End(xlUp)
.Offset(2, 0).Formula = "=Sum($B$6:" & _
.Address & ")"
End With
End If

"BillyRogers" wrote:

I have this code which returns a recordset from Access and pastes the results
in Excel. I need it to totall columns B and C.
The only problem is that the query will return a different number of rows
each month, so I don't know how to code the sum formula.


SQLcmd = "SELECT * FROM [S 06 Safelite Fee Summary]"

rs.Open Source:=SQLcmd, _
ActiveConnection:="Provider=Microsoft.Jet.OLEDB.4. 0; Data Source=G:\Brad and
Mary's DB.mdb" + _
"; User Id=admin; Password="


Column = 1
Row = 1

For Each fld In rs.Fields

Cells(Row, Column).Value = fld.Name
Cells(Row, Column).Select
With Selection.Interior
.ColorIndex = 15
.Pattern = xlSolid

End With
Selection.Font.Bold = True


Column = Column + 1

Next fld

Cells(2, 1).CopyFromRecordset rs

Cells.Select
Cells.EntireColumn.AutoFit

Range("A65000").End(xlUp).Offset(1, 0).Activate
ActiveCell.Value = "Fees Subtotal"



--
Billy Rogers

Dallas,TX

Currently Using SQL Server 2000, Office 2000 and Office 2003