VBA to Sum last row of column based on data from Column A

[libby845]

Hello -
I have a spreadsheet that has VBA to sum the final row.
Range("B" & LastRow + 1).Formula = "=(SUM(B10:B" & LastRow & "))"

This worked perfectly until areas of responsibility subtotals were added to the report.
For example Oncology 20
250011130 - 4 East 15
250013456 - 2 North 5
Women and Children 33
250015434 - Pediatrics 18
250014020 - 3 West 5
250036578 - 3 North 10

The current macro sums everything together with a result of 106, however the result we want is 53. I thought about dividing by 2, but I can't because at times the users hide the detail and only show the Area of responsibility, so the result would be incorrect.
The number of departments below Area of responsibility changes constantly as do the total departments.
I think I need a SUMIF type command based on if it starts with a character sum it, and if it starts with a number ignore. I don't know how to write that into the macro statement.

Can anyone help?

 

[libby845]

Just to note the code starts in row 1.
Would it be that there may be text in col B at that point, rather than a number ??
That will give you a runtime 13 error "Type Mismatch"

Hi Michael - Thanks for still putting up with me. That is the error message. The data starts in row 10. I tried changing A to A10, but that didn't work.

 

[Michael M]

It's always best to provide ALL information when asking for VBA code....VBA is very logical and requires exact info....try this

Sub MM1()
Dim lr As Long, r As Long, x As Double
lr = Cells(Rows.Count, "A").End(xlUp).Row
x = 0
y = 0
Z = 0
For r = [color=red]10[/color] To lr
    If not IsNumeric(Mid(Range("A" & r).Value, 1, 1)) Then
    x = x + Range("B" & r).Value
    y = y + Range("G" & r).Value
    Z = Z + Range("H" & r).Value
    End If
Next r
Range("B" & lr + 1).Value = x
Range("G" & lr + 1).Value = y
Range("H" & lr + 1).Value = Z
End Sub

 

[libby845]

Sorry about that!! This works great! Thank you so much for your help!!

 

[Michael M]

Glad to help...