Hi all - I am trying to assign the sum and number of filtered results
to variables per below after filtering..

// FILTER ALLOCS SHEET
Sheets("Allocs").Range("A1").AutoFilter Field:=4, Criteria1:=TapsRic
Sheets("Allocs").Range("A1").AutoFilter Field:=5, Criteria1:=TapsDir

//ASSIGN SUBTOTAL VALUES TO VARIABLES
AllocsCount =
Sheets("Allocs").Application.WorksheetFunction.Sub total(3,
Range("G:G")) - 1
AllocsSum = Sheets("Allocs").Application.WorksheetFunction.Sub total(9,
Range("G:G"))

Now this works fine if I activate the "Allocs" sheet prior to running
those last 2 lines - however, as it is in a loop and it goes through
hundreds of lines it results in the screen flickering between 2 sheets
which is annoying. Is there anyway that I can achieve the same without
activating the sheet? It seems that the two variables when I MsgBox
them are the sum and number of rows on the other sheet that i want
active the whole time.

Thanks for any help
Will

Activation may be required, select rarely is, but to keep from seeing it
happen use :
Application.ScreenUpdating = False
'Do some fancy code
Application.ScreenUpdating = True

Mike F

wrote in message
oups.com...
Hi all - I am trying to assign the sum and number of filtered results
to variables per below after filtering..

// FILTER ALLOCS SHEET
Sheets("Allocs").Range("A1").AutoFilter Field:=4, Criteria1:=TapsRic
Sheets("Allocs").Range("A1").AutoFilter Field:=5, Criteria1:=TapsDir

//ASSIGN SUBTOTAL VALUES TO VARIABLES
AllocsCount =
Sheets("Allocs").Application.WorksheetFunction.Sub total(3,
Range("G:G")) - 1
AllocsSum = Sheets("Allocs").Application.WorksheetFunction.Sub total(9,
Range("G:G"))

Now this works fine if I activate the "Allocs" sheet prior to running
those last 2 lines - however, as it is in a loop and it goes through
hundreds of lines it results in the screen flickering between 2 sheets
which is annoying. Is there anyway that I can achieve the same without
activating the sheet? It seems that the two variables when I MsgBox
them are the sum and number of rows on the other sheet that i want
active the whole time.

Thanks for any help
Will

Range("G:G") is unqualified. In most cases, it'll refer to the active sheet.

You could use:

AllocsCount = Application.Subtotal(3, Worksheets("Allocs").Range("G:G")) - 1

so that you're more specific.

" wrote:

Hi all - I am trying to assign the sum and number of filtered results
to variables per below after filtering..

// FILTER ALLOCS SHEET
Sheets("Allocs").Range("A1").AutoFilter Field:=4, Criteria1:=TapsRic
Sheets("Allocs").Range("A1").AutoFilter Field:=5, Criteria1:=TapsDir

//ASSIGN SUBTOTAL VALUES TO VARIABLES
AllocsCount =
Sheets("Allocs").Application.WorksheetFunction.Sub total(3,
Range("G:G")) - 1
AllocsSum = Sheets("Allocs").Application.WorksheetFunction.Sub total(9,
Range("G:G"))

Now this works fine if I activate the "Allocs" sheet prior to running
those last 2 lines - however, as it is in a loop and it goes through
hundreds of lines it results in the screen flickering between 2 sheets
which is annoying. Is there anyway that I can achieve the same without
activating the sheet? It seems that the two variables when I MsgBox
them are the sum and number of rows on the other sheet that i want
active the whole time.

Thanks for any help
Will

--

Dave Peterson

Wonderful - both methods solved the problem but since I would like to
see progress on the other worksheet I have gone with the 2nd method.
Cheers guys

On Jan 27, 3:27 pm, Dave Peterson wrote:
Range("G:G") is unqualified. In most cases, it'll refer to the active sheet.

You could use:

AllocsCount = Application.Subtotal(3, Worksheets("Allocs").Range("G:G")) - 1

so that you're more specific.



" wrote:

Hi all - I am trying to assign the sum and number of filtered results
to variables per below after filtering..

// FILTER ALLOCS SHEET
Sheets("Allocs").Range("A1").AutoFilter Field:=4, Criteria1:=TapsRic
Sheets("Allocs").Range("A1").AutoFilter Field:=5, Criteria1:=TapsDir

//ASSIGN SUBTOTAL VALUES TO VARIABLES
AllocsCount =
Sheets("Allocs").Application.WorksheetFunction.Sub total(3,
Range("G:G")) - 1
AllocsSum = Sheets("Allocs").Application.WorksheetFunction.Sub total(9,
Range("G:G"))

Now this works fine if I activate the "Allocs" sheet prior to running
those last 2 lines - however, as it is in a loop and it goes through
hundreds of lines it results in the screen flickering between 2 sheets
which is annoying. Is there anyway that I can achieve the same without
activating the sheet? It seems that the two variables when I MsgBox
them are the sum and number of rows on the other sheet that i want
active the whole time.

Thanks for any help
Will--

Dave Peterson