Find Last Empty Cell in a Column & then Sum all the Cells Above?

[jassyphee]

Currently the data has blank cells in between, every time I sum of the numbers it only sums the first couple of cells and places the total in the empty cell. So the column A is the headings and description of amounts, and column B is the amount. As well, the number of entries vary all the time, therefore differing rows. I want to do a sum total of column B in the last row of the column (of all those rows). As well, I have multiple sheets running, but this is the only sheet that has blank spaces due to heading issues. The following is the copy of the code:

Function calSum(sheetName As String, startCol As String, startRow As Integer, _
                refName As String, sumTitle As String)
    Sheets(sheetName).Activate
    
    Dim row As Integer
    
    row = startRow
     Do While Not IsEmpty(Range(startCol & row))
        row = row + 1
    Loop
    
    
    Range(startCol & row).Formula = "=sum(" & startCol & startRow & ":" & startCol & row - 1 & ")"
    With Range(startCol & row).Borders(xlEdgeTop)
        .LineStyle = xlContinuous
    End With
    With Range(startCol & row).Borders(xlEdgeBottom)
        .LineStyle = xlDouble
    End With
    
    
    Range(startCol & row).Offset(0, -1).Value = sumTitle
    Range(startCol & row).Offset(0, -1).Font.Bold = True
    
    
    Dim nameCheck As Range
    On Error GoTo rangeError
    Set nameCheck = Range(refName)
    ActiveWorkbook.Names(refName).Delete
    
rangeError:
    Range(startCol & row).Name = refName
    
End Function
    
Sub getTotals()
    Dim nm As Name
    For Each nm In ThisWorkbook.Names
        Dim n As String
        n = Split(nm, "!")(0)
        If Right(n, Len(n) - 1) <> "Investment" Then
            Range(nm).Offset(0, -1).Value = ""
        Else
            Range(nm).Offset(-2, -3).Clear
            Range(nm).Offset(-2, -2).Clear
            Range(nm).Offset(-2, -0).Clear
            
            Range(nm).Offset(2, -3).Clear
            Range(nm).Offset(2, -2).Clear
            Range(nm).Offset(2, 0).Clear
            
            Range(nm).Offset(0, -3).Clear
            
        End If
        Range(nm).Clear
        nm.Delete
    Next nm
    
    calSum "Original Assets", "B", 7, "Total_Assets", "Total Assets"
    calSum "Cap. Rec.", "D", 5, "Total_Receipts", "Total Receipts"
    calSum "Cap. Disb.", "D", 5, "Total_Disbursements", "Total Disbursements"
    calSum "Rev. Rec.", "D", 5, "Total_Revenue_Receipts", "Total Revenue Receipts"
    calSum "Rev. Disb.", "D", 5, "Total_Revenue_Disbursements", "Total Revenue Disbursements"
    calSum "Unrealized", "B", 3, "Unrealizedoriginalassets", "Total"
    calSum "Invest on hand", "B", 3, "Invest_On_Hand", "Total"
    calInvestment "Investment", "D", "F", 5

End Sub Sub clearTotals()
 Dim nm As Name
    For Each nm In ThisWorkbook.Names
        Dim n As String
        n = Split(nm, "!")(0)
        If Right(n, Len(n) - 1) <> "Investment" Then
            Range(nm).Offset(0, -1).Value = ""
        Else
            Range(nm).Offset(-2, -3).Clear
            Range(nm).Offset(-2, -2).Clear
            Range(nm).Offset(-2, -0).Clear
            
            Range(nm).Offset(2, -3).Clear
            Range(nm).Offset(2, -2).Clear
            Range(nm).Offset(2, 0).Clear
            
            Range(nm).Offset(0, -3).Clear
            
        End If
        Range(nm).Clear
        nm.Delete
    Next nm
End Sub

 

[BrianMH]

This will sum column b on the sheet name you specify and put that sum in the cell below the last entry in column B.

Sub SumColumnB()
    Dim r As Range
    With Sheets("YourSheetName")
        Set r = .Range("B" & .Rows.Count).End(xlUp).Offset(1)
        r.Value = WorksheetFunction.Sum(.Range("B:B"))
    End With
End Sub

 

[jassyphee]

Thank you so much! this worked perfectly!

This will sum column b on the sheet name you specify and put that sum in the cell below the last entry in column B.

Sub SumColumnB()
    Dim r As Range
    With Sheets("YourSheetName")
        Set r = .Range("B" & .Rows.Count).End(xlUp).Offset(1)
        r.Value = WorksheetFunction.Sum(.Range("B:B"))
    End With
End Sub