how to do sum when meet an empty line in vba
 

dear all, how to sum up the total for each of the category below in
vba? when next row is empty then can do a sum up. for example a shell
and head will be 4650 when encounter an empty row after linda.
can help. thanks


a shell and head
andy 50
sussy 100
linda 4500

b nozzle
james 98
zortan 340

c manhole
bryan 34
fendi 444

how to do sum when meet an empty line in vba
 

Here is some code

Dim i As Long
Dim sHead As String
Dim nTotal

sHead = Left(Range("A1").Value, 1)
nTotal = 0

For i = 1 To Cells(rows.Count, "A").End(xlUp).Row
If Cells(i, "A").Value = "" Then
Cells(i, "A").Value = "Total for " & sHead
Cells(i, "B").Value = nTotal
sHead = Left(Cells(i + 1, "A").Value, 1)
nTotal = 0
Else
nTotal = nTotal + Cells(i, "B").Value
End If
Next i
Cells(i, "A").Value = "Total for " & sHead
Cells(i, "B").Value = nTotal


--

HTH

RP

"tango" wrote in message
om...
dear all, how to sum up the total for each of the category below in
vba? when next row is empty then can do a sum up. for example a shell
and head will be 4650 when encounter an empty row after linda.
can help. thanks


a shell and head
andy 50
sussy 100
linda 4500

b nozzle
james 98
zortan 340

c manhole
bryan 34
fendi 444