WorksheetFunction.Sum & AutoFilter - sums all values not the filtered 2

[itlee]

I am using VBA to filter some data and I want to get a total for the filtered data.

I have used:

[COLOR=green]'AutoFilter code here...[/color]
Set myRange = ActiveWorksheet.Range("D:D")
dblTotal = App.WorksheetFunction.Sum(myRange)

However, this returns the sum of all the values not the filtered ones.

Anyone got any ideas on how to sum up the filtered values?

I have had a look at subtotal, but I am unable to get the correct figure. I could copy & paste the filtered data to a new sheet, but there are a lot of these to do and it may take to long do it this way.


ITLee. MCP\Dev\Prog\DBA\ITIL

 

[SkipVought]

Hi,

Instead of using the SUM function, use the SUBTOTAL function.

Skip,

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

 

[gh61]

Try:

Set myRange = Range("H:H").SpecialCells(xlCellTypeVisible)
    dblTotal = Application.WorksheetFunction.Sum(myRange)

HTH

gh

 

[itlee]

Thanks, gh61

Your suggestion helped, but what I have discovered is that you need to re-assign myrange after each filter:

[COLOR=green]'1st Filter[/color]
Selection.AutoFilter Field:=3, Criteria1:="1"
[b]Set myRange = ActiveWorksheet.Range("D:D").SpecialCell (xlCellTypeVisible)[/b]
dblTotal = App.WorksheetFunction.Sum(myRange)

[COLOR=green]'2nd Filter[/color]
Selection.AutoFilter Field:=3, Criteria1:="2"
[b]Set myRange = ActiveWorksheet.Range("D:D").SpecialCell (xlCellTypeVisible)[/b]
dblTotal = App.WorksheetFunction.Sum(myRange)

[COLOR=blue]etc...[/color]

otherwise myRange still points to the initial range and will keep on returning that summed value.

Cheers, I'll award you the star.



ITLee. MCP\Dev\Prog\DBA\ITIL

 

[SkipVought]

set myRange = ActiveWorksheet.Range("D:D")
dblTotal = Application.WorksheetFunction.SUBTOTAL(9,myRange)

ALWAYS works with AutoFilter!

Skip,

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

 

[gh61]

I posted mine before I saw Skip's, and it is of course a much better solution. I've never really looked at subtotal before, but I will now!

Where do I hand my star back?

 

[SkipVought]

gh61,

No need to return star! Believe me, I've been in the SAME place myself!

Always nice to have a quiver full of various types of arrows from which to choose, to launch against a problem.

There is more than ONE way to skin a cat! ;-)

Skip,

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

 

[itlee]

cheers guys,

I worked out that I was using the wrong function integer in subtotal earlier today, thats why I never got the correct figure.

thanks for you input.

ITLee. MCP\Dev\Prog\DBA\ITIL