determine range size after auto filter?

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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)
 
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.
 
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
 
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.
 
Back
Top