Auto Sum in a Macro

cpezzer

New Member
Joined
Dec 21, 2005
Messages
2
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
 

Some videos you may like

Excel Facts

Select all contiguous cells
Pressing Ctrl+* (asterisk) will select the "current region" - all contiguous cells in all directions.

chiello

Well-known Member
Joined
Jan 18, 2005
Messages
848
Hi cpezzer, the following code works with data in Column A stsrting from A1. Post for feedback

Ciao

Andrea


Code:
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
 

Watch MrExcel Video

Forum statistics

Threads
1,118,069
Messages
5,570,010
Members
412,304
Latest member
citrus
Top