Auto Sum in a Macro

[cpezzer]

Hi I have created the following code to autosum a large number of rows of data, around 80000 in total, which is far too slow to do manaually.

The code works fine in terms of a loop and perfectly if you run the insert autosum as a single function outside of the loop. However the two together clash and im not sure how to resolve this. i can leave this running overnight so if it takes a tad longer to do it a different way thats fine.

Can anyone please help me resolve why it jams?

Thanks

Chris


Sub autosumloop()

Do Until ActiveCell = Empty And ActiveCell.Offset(1) = Empty

If ActiveCell = Empty Then
CommandBars.FindControl(ID:=226).Execute
Application.SendKeys ("~")
Application.SendKeys ("~")
Else
ActiveCell.Offset(1).Select
End If


Loop
End Sub

 

[cpezzer]

Can anyone help, please

 

[cpezzer]

Can anyone help, please

 

[cpezzer]

Can anyone help, please

 

[chiello]

Hi cpezzer, the following code works with data in Column A stsrting from A1. Post for feedback

Ciao

Andrea

Sub AutoSum()
'
' AutoSum Macro
'
Dim SumCell As Range, FirstCell As Range, LastCell As Range
If Range("A1") <> "" Then
    Set FirstCell = Range("A65536").End(xlUp).Offset(2, 0)
    While FirstCell.Row > 1
        Set SumCell = FirstCell.Offset(-1, 0)
        Set LastCell = SumCell.Offset(-1, 0)
        If LastCell.Offset(-1, 0) = "" Then
            Set FirstCell = LastCell
        Else
            Set FirstCell = LastCell.End(xlUp)
        End If
        With SumCell
            .Formula = "=SUM(" & Range(FirstCell, LastCell).Address(False, False, xlA1) & ")"
            .Font.Bold = True
            .Font.ColorIndex = 3
        End With
    Wend
End If
End Sub

 

[cpezzer]

Thanks

Hi,

Thanks very much, this works perfectly.

Chris