Summing columns in Excel

[RicksAtWork]

Using the following code I am filtering a worksheet.

Set individuals = UniqueItemCollection(Range("Data_Individuals"))

For Each individual In individuals

Selection.AutoFilter
Selection.AutoFilter Field:=1, Criteria1:=CStr(individual)


//Code here

Next individual

This works very well.

This data now needs to be aggregated i.e. there may be 5 rows for each individual - I want to merge this into one row - without using a pivot table.

I want to use VBA.

How should I approach this - note that some columns are text based i.e. Country = Japan others are numerical i.e. Score =10

 

[SkipVought]

Hi,

Since you are using the AutoFilter, you can aggregate using the spreadsheet function SUBTOTAL. first argument, 9, sums visible cells.


Skip,

_{[red]Be Advised![/red]
A wee deranged psychic may be runnin' around out there!
SMALL MEDUIM @ LARGE}

 

[RicksAtWork]

Thanks!!

So SumTotal(9,???)

What do I put in the second arguement, I dont know how many rows there are - I only know that I want column 2 to be totalled over the visible range???

 

[SkipVought]

if your values are in column A...
[tt]
=SUBTOTAL(9,A:A)
[/tt]


Skip,

_{[red]Be Advised![/red]
A wee deranged psychic may be runnin' around out there!
SMALL MEDUIM @ LARGE}

 

[RicksAtWork]

I get a function not defined error - I am using Excel 2003!

 

[SkipVought]

And how are you using it in your code?


Skip,

_{[red]Be Advised![/red]
A wee deranged psychic may be runnin' around out there!
SMALL MEDUIM @ LARGE}

 

[RicksAtWork]

It seems that Subtotal runs from Selection.SubTotal ...

?

I dont want to loose my reference to the selection - because it takes lots of filtering to get hold of it.

So, using the Subtotal function how do I specify that I want to sum column b in reference to this selection without loosing the selection???

I'm going mad!

 

[RicksAtWork]

I tried the code exactly as you put it at first!

 

[SkipVought]

If you use a spreadsheet function ON A SHEET...
[tt]
=SUBTOTAL(9,A:A)
[/tt]
in VB code...

MySum = Application.SUBTOTAL(9, MySheet.Range("A:A"))

Skip,

_{[red]Be Advised![/red]
A wee deranged psychic may be runnin' around out there!
SMALL MEDUIM @ LARGE}

 

[RicksAtWork]

Cheers