I am using Office 2003 on Windows XP.

I need VBA code to enter subtotals in several columns of numbers. The
structure of the sheet does not lend itself to using the built-in subtotal
command. The data is already divided into clusters with two row breaks
between each cluster. Each cluster needs to be subtotaled and each cluster
varies in length from 1 to several thousand rows. The sheet contains over
32,000 rows and the structure and length are dynamic (change daily). The data
is assembled by several different persons each adding their chunk(s) of data
from different sources into different sheets. I'm trying to help automate
putting the whole thing together.

1. I can't come up with a way to do this apart from looping thru the cells,
but I'm afraid this method will take too long to complete (I haven't
developed it yet, as I'm waiting until I get ideas from this post). Using
this method, is it faster to collect the target addresses first in an array
and then write formulas into those cells, or just For-Each-Cell straight thru?

2. I could have sworn that in previous versions of Excel, you could select a
column of values like I have described and you could click the AutoSum button
and it would enter totals automatically beneath each cluster for you, but my
Office 2003 version doesn't do this - or is there some other trick to that -
or am I just loosing it altogether?

Any ideas for a high speed solution?

Thanks much in advance.

You have empty cells between each group?

You can pick out a column that "controls" the size of the group (I used column
D)?

And the entries in that column are constants, not formulas?

If yes to all:

Option Explicit
Sub testme()

Dim myRng As Range
Dim myArea As Range

With Worksheets("Sheet1")
Set myRng = Nothing
On Error Resume Next
Set myRng = .Range("D2", .Cells(.Rows.Count, "D").End(xlUp)) _
.Cells.SpecialCells(xlCellTypeConstants)
On Error GoTo 0

If myRng Is Nothing Then
MsgBox "no constants!"
Exit Sub
End If

For Each myArea In myRng.Areas
With myArea
.Resize(1).Offset(.Cells.Count).Formula _
= "=sum(" & .Address(0, 0) & ")"
.Resize(1).Offset(.Cells.Count, 1).Formula _
= "=sum(" & .Offset(0, 1).Address(0, 0) & ")"
End With
Next myArea
End With

End Sub

This portion just adds a formula to the cell to the right:

.Resize(1).Offset(.Cells.Count, 1).Formula _
= "=sum(" & .Offset(0, 1).Address(0, 0) & ")"

You could add as many of these as you want--just make sure the .offset()'s are
what you want.

quartz wrote:

I am using Office 2003 on Windows XP.

I need VBA code to enter subtotals in several columns of numbers. The
structure of the sheet does not lend itself to using the built-in subtotal
command. The data is already divided into clusters with two row breaks
between each cluster. Each cluster needs to be subtotaled and each cluster
varies in length from 1 to several thousand rows. The sheet contains over
32,000 rows and the structure and length are dynamic (change daily). The data
is assembled by several different persons each adding their chunk(s) of data
from different sources into different sheets. I'm trying to help automate
putting the whole thing together.

1. I can't come up with a way to do this apart from looping thru the cells,
but I'm afraid this method will take too long to complete (I haven't
developed it yet, as I'm waiting until I get ideas from this post). Using
this method, is it faster to collect the target addresses first in an array
and then write formulas into those cells, or just For-Each-Cell straight thru?

2. I could have sworn that in previous versions of Excel, you could select a
column of values like I have described and you could click the AutoSum button
and it would enter totals automatically beneath each cluster for you, but my
Office 2003 version doesn't do this - or is there some other trick to that -
or am I just loosing it altogether?

Any ideas for a high speed solution?

Thanks much in advance.

--

Dave Peterson

In the words of Gomer Pyle, shazzam!

Thanks a lot Dave! I never thought of using special cells like that!

"Dave Peterson" wrote:

You have empty cells between each group?

You can pick out a column that "controls" the size of the group (I used column
D)?

And the entries in that column are constants, not formulas?

If yes to all:

Option Explicit
Sub testme()

Dim myRng As Range
Dim myArea As Range

With Worksheets("Sheet1")
Set myRng = Nothing
On Error Resume Next
Set myRng = .Range("D2", .Cells(.Rows.Count, "D").End(xlUp)) _
.Cells.SpecialCells(xlCellTypeConstants)
On Error GoTo 0

If myRng Is Nothing Then
MsgBox "no constants!"
Exit Sub
End If

For Each myArea In myRng.Areas
With myArea
.Resize(1).Offset(.Cells.Count).Formula _
= "=sum(" & .Address(0, 0) & ")"
.Resize(1).Offset(.Cells.Count, 1).Formula _
= "=sum(" & .Offset(0, 1).Address(0, 0) & ")"
End With
Next myArea
End With

End Sub

This portion just adds a formula to the cell to the right:

.Resize(1).Offset(.Cells.Count, 1).Formula _
= "=sum(" & .Offset(0, 1).Address(0, 0) & ")"

You could add as many of these as you want--just make sure the .offset()'s are
what you want.

quartz wrote:

I am using Office 2003 on Windows XP.

I need VBA code to enter subtotals in several columns of numbers. The
structure of the sheet does not lend itself to using the built-in subtotal
command. The data is already divided into clusters with two row breaks
between each cluster. Each cluster needs to be subtotaled and each cluster
varies in length from 1 to several thousand rows. The sheet contains over
32,000 rows and the structure and length are dynamic (change daily). The data
is assembled by several different persons each adding their chunk(s) of data
from different sources into different sheets. I'm trying to help automate
putting the whole thing together.

1. I can't come up with a way to do this apart from looping thru the cells,
but I'm afraid this method will take too long to complete (I haven't
developed it yet, as I'm waiting until I get ideas from this post). Using
this method, is it faster to collect the target addresses first in an array
and then write formulas into those cells, or just For-Each-Cell straight thru?

2. I could have sworn that in previous versions of Excel, you could select a
column of values like I have described and you could click the AutoSum button
and it would enter totals automatically beneath each cluster for you, but my
Office 2003 version doesn't do this - or is there some other trick to that -
or am I just loosing it altogether?

Any ideas for a high speed solution?

Thanks much in advance.

--

Dave Peterson