Automating Sum totals

Question

I have an in-house program at work which spits out a "report" in Excel. It comes out formatted as the table on the left, in the image. I am trying to program a macro for users that automatically sums up all of the line items into the header-line (as on the right, formulas shown)

(Note: Values that are '0' are simply blank cells)

My actual question is for an easy way to determine the final row in an Excel file. Cell.End(xlDown) and Count functions have only gotten me so far. But I'm posting my whole problem in case someone has any clever ideas.

Answer 1

P.

You can create your own function to solve this problem. For example:

Public Function SumNew() As Long

Dim Col_ As Long
Dim Row_ As Long
Dim LastRow As Long

Col_ = Application.ThisCell.Column
Row_ = Application.ThisCell.Row

LastRow = Cells.Find("*", searchorder:=xlByRows, searchdirection:=xlPrevious).Row

SumNew = 0

For i = Row_ + 1 To LastRow
    If Cells(i, 1) <> vbNullString Then Exit Function 'if you find next filled 'Line Group#' end sums
    SumNew = SumNew + Cells(i, Col_).Value
Next i

End Function

For sample data, it looks like it works:

Answer 2

This is not very elegant but should work. If you know that the reports that are imported into Excel are never going to be longer than a certain number of lines (no more than 10000, for example), then choose and even larger number (say 50000), go to that row and then Selection.End(xlUp). Something like this:

Range("B50000").Select
Selection.End(xlUp).Select
LastRow = ActiveCell.Row

This is assuming that the last row of your report will have content in column B, which seems to be the case in the examples you show.