Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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) |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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) |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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) |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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) |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Auto Filter - drop down (increase text size) | Excel Discussion (Misc queries) | |||
size limitations on auto filter | Excel Discussion (Misc queries) | |||
How a macro auto-determine unspecified range | Excel Programming | |||
How to determine a range size | Excel Programming | |||
In VB, get Auto Filter Range | Excel Programming |