Sum Blank

[billandrew]

Hello All

Trying to sum range above when cell is Blank. Using the below code.

Sub SumBlank()Dim lr&
Dim i&
lr = Cells(Rows.Count, 1).End(xlUp).Row
total = 0


With ActiveSheet
For i = 1 To lr + 1
If Cells(i, 1) <> "" Then
total = total + Cells(i, 1)
End If
Next
End With
End Sub

 

[Fluff]

What exactly is your question?

 

[Rick Rothstein]

Trying to sum range above when cell is Blank.

Can you describe in more detail exactly what you mean by the above? Some examples would be helpful.

 

[billandrew]

Looking to insert the sum of the above in the blank cells.

Thank You

12
12
20
35
230
120
250
125

<tbody>
</tbody>

 

[Fluff]

Maybe

Sub SumBlank()
Dim lr As Long, i As Long
Dim total As Double
lr = Cells(Rows.Count, 1).End(xlUp).Row
total = 0


With ActiveSheet
   For i = 1 To lr + 1
      If .Cells(i, 1) <> "" Then
         total = total + .Cells(i, 1)
      Else
         .Cells(i, 1) = total
         total = 0
      End If
   Next i
End With
End Sub

 

[James006]

Hi,

You could use an Array Formula in cell B2 :

=IF(A2="",IFERROR(1/(1/SUM(A2:INDEX(A$1:A1,MATCH(9.99999999999999E+307,1/(1-ISNUMBER(A$1:A1)))))),""),"")

HTH

 

[Rick Rothstein]

Assuming your numerical values are constants (that is, that they are not the result of formulas in the cells), give this macro a try...

[table="width: 500"]
[tr]
	[td]Sub SumToBlanks()
  Dim Ar As Range
  For Each Ar In Columns("A").SpecialCells(xlConstants).Areas
    Ar(1).Offset(Ar.Count).Value = Application.Sum(Ar)
  Next
End Sub[/td]
[/tr]
[/table]

 

[billandrew]

@ Fluff Works perfect. Why is there a need to include an else.

@ Mr. Rothstein. Missed this one - using the SpecialCells Method is also adequate.

Thank you

 

[billandrew]

James

Thank You for the formula option, however the first two cells in the range are not totaled using this method.

 

[Fluff]

@ Fluff Works perfect. Why is there a need to include an else.

If the cell is blank it then puts the total into that cell & clears the total back to zero.