Find Empty Cell and Sum Cells Above

[camdevl09]

I am trying to set up a macro that will go down a column until the next empty row and then sum the rows above it. I have a macro right now that will accomplish this, but it only inserts the value not the actual equation itself. For example,


1

2

empty cell <---- find this empty cell and Sum the two rows above it (3)

3

4

5

6

empty cell <---- find this empty cell and Sum the 4 rows above it (18)

7

empty cell <---- find this empty cell and sum the row above it (7)


this is the macro I have so far....


Sub Fill()

Dim aArea As Range

For Each aArea In Columns("P").SpecialCells(xlCellTypeConstants).Areas

Cells(aArea.Row + aArea.Rows.Count, 16).Formula = WorksheetFunction.Min(Range(Cells(aArea.Row, 16), Cells(aArea.Row + aArea.Rows.Count - 1, 16)))

Cells(aArea.Row + aArea.Rows.Count, 22).Formula = WorksheetFunction.Sum(Range(Cells(aArea.Row, 22), Cells(aArea.Row + aArea.Rows.Count - 1, 22)))

Next aArea

End Sub


Thanks!

 

[Luke M]

Needs you to tweak a few things (start row, end row, but this macro was written for use on column A. Hope it helps:

Sub InsertTotals()

Dim StartRow As Integer

Dim EndRow As Integer


StartRow = 1

EndRow = 11

For i = StartRow To EndRow

If Cells(i, "A") = "" And i > StartRow Then

Cells(i, "A").Formula = "=SUM(A" & StartRow & ":A" & i - 1 & ")"

StartRow = i + 1

End If

Next


End Sub

 

[camdevl09]

That works great, except for one thing....


for EndRow =


is there any may to make that the last cell that has value in it. (EndRow = End(xlup)) or anything like that?


Thanks!

 

[camdevl09]

Final Macro....


Dim StartRow As Integer

Dim EndRow As Integer


StartRow = 2

EndRow = Range("D65536").End(xlUp).Offset(1, 0).Row

For i = StartRow To EndRow

If Cells(i, "D") = "" And i > StartRow Then

Cells(i, "D").Formula = "=SUM(D" & StartRow & ":D" & i - 1 & ")"

StartRow = i + 1

End If

Next


End Sub


Thanks!!!

 

[Hui]

You could do this without code by adding a new column adjacent to your numbers

Put the same value in all cells and clear contents of the cells in the same rows as the blank ones in your data eg: =if(P2<>"",1,"") and copy down

Then use the Sub-Total Function (Data, Outline Subtotal)

 

[druid1980]

Camdevl09 that worked perfectly for me thanks very much.


One thing I am trying to do is to get the sum to display in column I. e.g. I would like the sum of D20 to D55 to appear in cell I55.


Does this makes sense?


I hope you can help, I have spent two days on this now.


Many thanks

 

[Faseeh]

Hi All,


I think we can achive this task of summing cells above empty/blank cells, using below formula:

=SUMPRODUCT((ISBLANK($B$3:$B$15)=TRUE)*($B$2:$B$14))

I have assumed that data is present in cells B3:B16, and we have our data to start always from second row downwards. This will give you sum of all the cells in this range above blank cell.


Thanks,

Faseeh

 

[desibabuji]

Hi Faseeh,


What if the number of rows is not fixed, unlike the above example where the data is between row 1 -15. What if the number of rows is not set. Can it be modified to accommodate that , maybe make it a variable and lookup the number of rows used?

I have a similar problem and will appreciate any input/help.

Thanks !

 

[kaushik03]

Hi desibabuji,


I am sure Fashee will reply.


Meanwhile, you can use dynamic named ranges using combination of offset countA and then pas the named range ti the formula (range). As you said that your data is from row 1 to 15, I would use a very generic example as follows:

Assuming,from A1 to A15 (of sheet1), below here is your data:


1

2

3

4

5

6

7

8

9

10

11

12

13

14

15


Now, press CTRL+F4 to bring the name manager, hit new, give some name to the name box (say MyRange), write the below formula to the refer to box and hit OK:


=OFFSET(Sheet1!$A$1,0,0,COUNTA(Sheet1!$A:$A,0)-1,1)


Now, to dead right hand corner of the refer to box, if you click the button, you will see that your range (A1:A15) gets selected( the selection would expand and shrink as and when you would add or delete any data to the range.Same way you could repeat the exercise to other column of data.


Now you can pass on this dynamic named range (MyRange) to your formula range when your row numbers are not fixed.


A very happy new year to you.


Kaushik

 

[desibabuji]

Thanks Kaushik,


That works. Also what do I need to change if I want to put the sum in the row "Above" the data and not the row at the end. So eg my data range is from 2 to 15 and I want to put the sum in row 1.


empty cell (Sum goes here = 15)

5

10

empty cell (sum goes here = 40)

10

10

10

10

and so on.


Thanks for your help

 

[Ajayvarrier]

There is a best workaround for this other than macro

1. Select the column till End of the Data (use ctrl + end then drag or other ways as you wish),

2. Press Ctrl + G

3. Select Special

4. Select Blanks and Press OK - Which will select all the blank cells in the column

5. Final Step - Press Alt + = keys - There you GO

Please see the attached File

 

[cyberx1]

Hi. I tried above with a formula constant and everything looked ok but only first occurrence of sum worked then stopped.

 

[Ajayvarrier]

Hi,

When i tried the Formula Constant is also working with the same method. Please try again and post the way you used if the error happens again

 

[Hareesh_KLD]

Sub GetTotal()


Dim i As Long

i = WorksheetFunction.Sum(Range(Range("A1"), Cells(Rows.Count, 1).End(xlUp)))

Range("b2").Value = i

End Sub

 

[Hareesh_KLD]

Sub GetTotal()

Dim i As Integer
Dim t As Integer

Range("A1").Select

i = Cells(Rows.Count, 1).End(xlUp).Row + 2

Do While ActiveCell.Row < i

t = WorksheetFunction.Sum(Range(ActiveCell, ActiveCell.End(xlDown)))


ActiveCell.End(xlDown).Offset(1, 0).Select

ActiveCell.Value = t

ActiveCell.Offset(1, 0).Select

Loop

End Sub

 

[Hareesh_KLD]

In Excel Use Following

Select range "A1:A18"
press F5 -->Special --> Blanks --> OK
now press Alt + =