Excel 2003 total a filtered list in VBA

Hi all,

I have an excel 2003 spreadsheet I'm creating for tracking troop number in battles for a game I play via a user form.
All is going well and I got over what I thought was the hard part of getting the data entry UI doing what it should.
Now it comes to the report part.

Consider a worksheet like this with these 5 columns
UserID (String), Battle (String), TroopType1 (Integer), Trooptype2(Integer), TroopType3(Integer)
Currently the user form for calculating totals of the troop types is applying an auto filter which works fine. I thought I could apply a subtotal on that to get the total number of trooptype1, trooptype2 etc... But for the life of my, I can't get it to go.
The code snippet below shows how I achieve the auto filter and it filters fine on the battle column. Now all I need is the total of column 3 (for TroopType1).

Also as a note I'm writeing the VBA and spreadsheet this using Excel 2007, but am wanting to to be compatable with Excel 2003 (So I'm saving at an excel 2003 spreadsheet).

Any help you could probive in my blond moment would be appriciated.

Set w = Worksheets("Race1")
w.Cells.AutoFilter field:=2, Criteria1:="=" & cmbBattle.Value

Open in new window

LVL 15
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

You don't even need VBA, just use this as the formula

The first parameters "9" refers to "SUM" and the second is the range that you are filtering on.  When an autofilter is applied, it automatically updated to sub-total only the ones showing.
qz8dswAuthor Commented:
Hi cyberkiwi,

But as I'm doing this all in a user form via VBA (It is dealing with more than one sheet and function), I am trying to use VBA to go through the 3-4 sheets, apply the filter on the battle (Both of which works), but getting the troop numbers from the filtered lists baring manually doing it is more problematic.

Sorry if I did not explain it better, but if it was one sheet I'd not be worried at all.

So to clarify I have the userform already going through multiple worksheets in the workbook, applying the filter (all sheets have the same columns, but different troop names), So I want to get a total from the filtered list via VBA as I don't think excel functions would be able to handle the fact the troop names are different on the worksheets and seperate them out for me.
I need to go through 3 worksheets and collate totals from all 3 as "seperate balances", I can only see me being able to do this and give me one report via VBA.

Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

Hi there,
What I meant was to add the formula just under your filtered table _on each sheet_ and then you can pull the values from those cells and sum them.
If you really had to, you can delete the cells after the operation.
Rory ArchibaldCommented:
You can use Subtotal in code too:

msgbox application.worksheetfunction.subtotal(9, w.range("C2:C1000"))

Open in new window

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
qz8dswAuthor Commented:
Thanks alot
Rory ArchibaldCommented:
Personally, I think a points split would have been fair here since cyberkiwi's suggested solution was eminently sensible and my post was simply a VBA interpretation of that. If you are amenable, I can get the question reopened for a split?
qz8dswAuthor Commented:
Hi rorya,

The reason why it was not a point split was i was after a VBA solution.
The tables themselves will be constantly growing in size, so having a predefined cell for the subtotals would not IMOHO be a great solution as it would have to be at around cell C20000 as an example.
You could use VBA to add the subtotal formula to the next empty cell via VBA and remove it as cyberkiwi suggested, but that in itself is messy and requiring more lines of code than your 1 line VBA interpretation.

Doing the subtotal command was not my problem, doing it in VBA was.

Rory ArchibaldCommented:
No problem - they're your points, after all! :)
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today

From novice to tech pro — start learning today.