vba code for multiple non continuous range in a column

[kishorvimal]

Hi,

Can some one help me with VBA code for getting sum in multiple range in a column.

I have numeric values in column B like B2 and B3 have some values and B4 and B5 are blank then B6, B7 and B8 have some values and B9 and B10 are blank. So, I want sum of B1 and B2 in B3 and sum of B6, B7 and B8 in B9 cell and same macro should work for this trend of sum in continuous filled multiple ranges in B column.

Many thanks in advance.


Vimal

 

[mirabeau]

Is it something like this that you want? (run code)

Sub sumnoncontig()
Dim e As Range
Set e = Range("B1")
If e = "" Then Set e = e.End(4)
Do
If e(2) = "" Then
    e.Offset(, 1) = e
    Set e = e.End(4)
Else
    e.End(4).Offset(, 1) = Application.Sum(Range(e, e.End(4)))
    Set e = e.End(4).End(4)
End If
Loop Until e.Row = Rows.Count
End Sub

 

[kishorvimal]

Thanks for the quick response. Actually I have basic knowledge of VBA. I want sum on 3rd, 9th and 12th cell in B column

Column
Row B
1 15
2 14
3 ---
5 ---
6 35
7 45
8 24
9 ---
10 ---
11 45
12 ---
13 ---




Thanks in advance....

Vimal

 

[mirabeau]

This one?

Sub sumnoncontig2()
Dim e As Range, x, y As Single
Set e = Range("B1")
If e = "" Then Set e = e.End(4)
Do
If e(2) = "" Then
    Set e = e.End(4)
Else
    x = e.End(4)(2).Address
    y = Application.Sum(Range(e, e.End(4)))
    Set e = e.End(4).End(4)
    Range(x) = y
End If
Loop Until e.Row = Rows.Count
End Sub

 

[kishorvimal]

you are champion....