PDA

View Full Version : Sum bold cells rows based on below non bold cells



sujittalukde
01-14-2008, 06:03 AM
I have attached a sample wb where the macro should sum all the cells below the bold cells based on column A till a next bold cell is found.
For eg.,
In the attached file at the cell B2,C2,D2 till last used column , sum should come from below cells till a next bold cell is found.
The Sum in the attached file is done manuaaly But I want this should be done by the macro.

Bob Phillips
01-14-2008, 02:12 PM
Public Sub ProcessData()
Dim i As Long
Dim LastRow As Long

With ActiveSheet

LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
StartRow = 2
For i = 3 To LastRow + 1

If .Cells(i, "A").Font.Bold Or i > LastRow Then

.Cells(StartRow, "B").Resize(, 3).FormulaR1C1 = "=SUM(R" & StartRow + 1 & "C:R" & i - 1 & "C)"

StartRow = i
End If
Next i
End With

End Sub

sujittalukde
01-14-2008, 10:13 PM
THanks xld, its working great.
One query:
You have used Public Sub ProcessData()
It also works if Public is ommitted. Then what is the significance of use of Public?
Many many thanks.

Bob Phillips
01-15-2008, 01:20 AM
Clarity.

sujittalukde
01-15-2008, 01:39 AM
Means using this
Public Sub ProcessData()
'
'
'
End Sub

and using this code
Sub ProcessData()
'
'
'
End Sub
gives me the same result.

Then why Public term is used?

Bob Phillips
01-15-2008, 02:16 AM
For Clarity, to make it clear that the sub is public. Public is the default, and I don't like defaults.

sujittalukde
01-15-2008, 03:10 AM
Thanks.