Tom My complaint is that I get a value for "Z" of 425, when in actual fact
the filtered range adds up to 385. So when I pass "Z" into a calculation to
select the last row I always get more rows than are actually filtered

"Tom Ogilvy" wrote:

xlApp.Range("b27") = xlApp.WorksheetFunction.Subtotal(3, xlRng)

should show the number of visible rows (assuming all visible rows in that
column are not empty).

it is hard to interpret "I get 385 rows" and "My range calculation shows
425"
to understand what your complaint is.

--
Regards,
Tom Ogilvy


"Jeff" wrote in message
...
I have a routine whereby I filter a range and then I want to select a
specific column down to the last row number, and then performa subtotal on
it.

When I run the code I get 385 rows. My range Calculation shows 425, the
total number of rows on the sheet.

Any Ideas?
================================================== ==
xlApp.Selection.AutoFilter
xlApp.Selection.AutoFilter Field:=26, Criteria1:="False"
xlApp.Selection.AutoFilter Field:=27, Criteria1:="False"
xlApp.Selection.AutoFilter Field:=15, Criteria1:="<=" & Now()
xlApp.Selection.AutoFilter Field:=22, Criteria1:=0
z = xlApp.ActiveSheet.AutoFilter.Range.Row - 1 +
xlApp.ActiveSheet.AutoFilter.Range.Rows.Count
Set xlRng = xlApp.Range(xlApp.Cells(4, 12), xlApp.Cells(z, 12))
xlApp.Selection.AutoFilter
xlApp.Sheets("Top Sheet").Select
xlApp.Range("a27").Formula = "Tasks Not Started"
xlApp.Range("b27") = xlApp.WorksheetFunction.Subtotal(3, xlRng)