VBA - Add SUM to every empty cell in column, but make the sum range the range of cells below the formula.

[Royzer]

Hi.

I need to find a way to search a specific column for blank cells and put a variable-range SUM formula in the blank cell above the range to be summed. This also includes cases where there is only one cell with data. I will still need a sum formula above that cell that contains the amount in the cell.

I've included an image example of the data I'll be working with, along with several examples of what I mean about the sum range. (I also have a workbook with the data in it, but i couldn't figure out how to upload it).

I would really appreciate any help I can get. Thank you.

 

[DanteAmor]

Try this:

Sub SumCells()
  Dim ar As Range
  For Each ar In Range("C3:C" & Range("C" & Rows.Count).End(xlUp).Row).SpecialCells(xlCellTypeConstants).Areas
    ar.Offset(-1).Resize(1).Value = WorksheetFunction.Sum(ar)
  Next
End Sub

 

[offthelip]

try this code

Sub test()
lastrow = Cells(Rows.Count, "C").End(xlUp).Row
inarr = Range(Cells(1, 3), Cells(lastrow + 1, 3))
brow = lastrow
For i = lastrow To 3 Step -1
   If inarr(i, 1) = "" And inarr(i + 1, 1) <> "" Then
    Cells(i, 3).Formula = "=Sum(C" & i & ":C" & brow & ")"
    brow = i
   End If
Next i

End Sub

 

[Royzer]

Thanks so much, DantaAmor. The code works perfectly!

Thank you as well, offthelip. For a reason I don't understand, the formulas went into the correct cells and the sum ranges are correct, but formula result for each of them is zero.

 

[DanteAmor]

Glad we could help & thanks for the feedback

 

[duongtruc1610]

Try this:

Sub SumCells()
  Dim ar As Range
  For Each ar In Range("C3:C" & Range("C" & Rows.Count).End(xlUp).Row).SpecialCells(xlCellTypeConstants).Areas
    ar.Offset(-1).Resize(1).Value = WorksheetFunction.Sum(ar)
  Next
End Sub

Deal sir. I use your code but ít not works. Can you write for 1 excel file end send me