determine range size after auto filter?
 

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)

determine range size after auto filter?
 

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)

determine range size after auto filter?
 

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)

determine range size after auto filter?
 

The code you show should return the row number of the last row in the
Autofilter range. I suspect the autofilter extends down to row 425.

--
Regards,
Tom Ogilvy

"jeff" wrote in message
...
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)