find next empty cell and add sum formula until last row of dat

This can be easily be accomplished via Pivot table.

I'm not sure of the exact purpose of this snippet of the code, but I created it as close to your specifications as possible. If part of it is not right, let me know if you need me to edit it for you.

One thing that I used judgment on is if there are consecutive blanks, I have it not adding the autosum there.

Sub Macro1()
 
Dim LastRow As Integer
Dim LastBlankRow As Integer
 
'tell it how many blank rows are at the top
LastBlankRow = 2 '(this would have to change if change the setup of the spreadsheet so that there is a different number of blank rows at the top)
 
'set last row
Range("A1").Select
LastRow = Cells(65536, 1).End(xlUp).Row
 
Range("AE3").Select
Do While ActiveCell.Row <= LastRow
    If ActiveCell.Value = "" Then
        ActiveCell.Formula = "=SUM(R[" & LastBlankRow + 1 - ActiveCell.Row & "]C:R[-1]C)"
        LastBlankRow = ActiveCell.Row
        If ActiveCell.Value = 0 Then
            ActiveCell.Value = ""
        End If
    End If
    ActiveCell.Offset(1, 0).Select
Loop
    
Range("A1").Select
    
    
End Sub

                                              

1:
2:
3:
4:
5:
6:
7:
8:
9:
10:
11:
12:
13:
14:
15:
16:
17:
18:
19:
20:
21:
22:
23:
24:
25:
26:
27:
28:

all

I would lilke code to include within a macro.

Basically I have  a spreadhseet that contains in excess of 2000 rows and 55 columns of HR data - on completion of data formatting etc i have approx 700 rows and 30 columns of data/information

There are duplicate records where an employee works in differing areas of the buisness or may work part and also as a casual.  In these instances there would be two active records,  However most staff only have one active record (but may have several inactive records in the system)  and based some code from chris
http://www.experts-exchange.com/Software/Office_Productivity/Office_Suites/MS_Office/Excel/Q_24757397.html

I have been able to 'remove' all duplicates with the exception of staff who have two active positions. (there are only 7 this month)

I need to be able to total their salary from both positions in the blank row and then copy the total into the second record and delete the first one (all information contained in reords that is required for my report is the same within each record - only differences are the salary and record number (rec # not needed)

I can run the macro to this stage and then manually complete the sum, cut paste and delete row but would prefer to include code and then continue with the remainder of the macro

I did try a pivot but doesn't do what I need - but thanks
I will try your code carmalize

rgds

Shaz

carmalize

I have included some of your code - thank you - and works fine except that the first caluclation does not include ae2 and I can't understand why

in this instance i need to sum ae2:ae3 and then ae5:ae6 etc.....

then all i need to do is find the code to remove the first record eg ae2 - i have tried the code
ActiveCell.ClearContents
ActiveCell.Offset(-2, 0).Select
         Selection.EntireRow.Delete
but it appears that .clearcontents does not remove the formula and while the row is deleted the result in the remianing row is 0

in addition I need to put in 'correct' the code to find the next blank cell and repeat the above

please see my code thus far

Sub Macrodelete3()


Dim LastRow As Integer
Dim LastBlankRow As Integer
 
'tell it how many blank rows are at the top
LastBlankRow = 2 '(this would have to change if change the setup of the spreadsheet so that there is a different number of blank rows at the top)
 
'set last row
Range("A1").Select
LastRow = Cells(500, 1).End(xlUp).Row
 
Range("AE3").Select
Do While ActiveCell.Row <= LastRow
    If ActiveCell.Value = "" Then
        ActiveCell.Formula = "=SUM(R[" & LastBlankRow + 1 - ActiveCell.Row & "]C:R[-1]C)"
        ActiveCell.Copy
        ActiveCell.Offset(-1, 0).Select
        Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
         ActiveCell.Offset(1, 0).Select
         ActiveCell.ClearContents
         ActiveCell.Offset(-2, 0).Select
         Selection.EntireRow.Delete
       
        LastBlankRow = ActiveCell.Row
        If ActiveCell.Value = 0 Then
            ActiveCell.Value = ""
        End If
    End If
    ActiveCell.Offset(1, 0).Select
Loop
   
Range("A1").Select
   
  End Sub

thanks all

apologies for the delay in responding - we had a three day weekend and i took an extra day.  carmalize i will try your suggestion today at some stage.

rgds

S

Carmalize -

works a treat - and it would be unlikely that a staff member would have three records and if so would need to do manually -

many thanks to you all

Shaz