Macro Multiple Data Sets in 1 sheet Varying Column Length Sum Function

[xcelnovice]

Hello,

I'd like to write a macro to sum Column D for each section of this sheet. Below is a sample but in my actual sheet the data continues on with various number of columns. I'm just learning macros have read how other threads but have trouble applying it to my sheet. So any notes explaining what the code is doing would be greatly appreciated.

Bread	White	Type 1	13.01%
		Type 2	22.49%
		Type 3	10.44%
		Type 4	33.09%
		Type 5	0.32%
		Type 6	0.34%
		Type 7	10.90%
		Type 8	9.41%
Bread	Rye	Type 1	39.98%
		Type 2	58.84%
		Type 3	0.57%
		Type 4	0.61%
Bread	Wheat	Type 1	55.49%
		Type 2	44.51%

<COLGROUP><COL style="WIDTH: 48pt" span=4 width=64><TBODY>
</TBODY>

 

[ParamRay]

.
.

Doesn't each group add up to 100% ?..

 

[xcelnovice]

Yes but not always

 

[ParamRay]

.
.

Assuming there's an empty row separating each group of percentages (as in your sample), then you could try something like this:

Sub SumAreas()

    Dim SRang As Range
    Dim SArea As Range
    
    On Error Resume Next
        With ActiveSheet.Columns("D")
            Set SRang = Union( _
                .SpecialCells(xlCellTypeConstants, xlNumbers), _
                .SpecialCells(xlCellTypeFormulas, xlNumbers))
        End With
    On Error GoTo 0
    
    If Not SRang Is Nothing Then
        For Each SArea In SRang.Areas
            With SArea.Offset(0, 1).Item(1)
                .Value = WorksheetFunction.Sum(SArea)
                .NumberFormat = "0.00%"
            End With
        Next SArea
    End If

End Sub