well i have made some progress. i can now get an accurate count of the
visible cells with the following
turn on filter, and filter
set filteredRange = ActiveSheet.AutoFilter.Range
filteredRange.columns(1).specialCells(xlcelltypeVi sible).count 'now
returns an accurate number of visible rows.

now my problem is that i can't figure out a way to access the data in
the filtered range. i must not understand the range ojbect very well,
but i would i think that with the filteredRange assignment that
filteredRange.cells(2,2) or filteredRange.cells(3,2) would give me the
visible cells which could be really (22,2) and (790,2), except it gives
me the literal cell value as if the sheet was not filtered. i also tried
filteredRange.specialcells(xlcelltypevisible).cell s(3,2), yet with the
same results.

any ideas on how to access the data only in the visible without copying
it.

thanks for you help so far

bst

Dave Peterson wrote in
:

The code I suggested is after the range has had the autofilter arrows
applied.

And most likely after you've filtered by your criteria.

It was in addition to your filtering code--not a replacement for that
portion.

bst wrote:

Hi Dave,
the second option does not seem to work.
the first with line fails, nor does it allow me to set the autofilter
criteria. i believe you are leading me in the right direction, but it
seems as if i need more detail. i will continue to follow this lead.
if you or anyone else seems to have any more insight it would be
greatly appreciated. i'm using excel 2003 if that matters.

i'm now trying:
.Range("B1").AutoFilter field:=2, Criteria1:=uniqueSerials(xCtr)
Set filteredRange = .Cells.SpecialCells(xlCellTypeVisible)

i will attempt you column method, but i wonder if it will set my
range to just the column filtered, when i infact want to examine
manipulate other column's data. from more results i've examined, it
is the set filteredRange line that is working incorectly.

thanks
bst

Dave Peterson wrote in
:

Try this:

Option Explicit
Sub testme01()
Dim myRng As Range
Set myRng = ActiveSheet.Range("a1,a3").EntireRow
MsgBox myRng.Address & vbLf & myRng.Rows.Count

Set myRng = ActiveSheet.Range("a1:a5,a7").EntireRow
MsgBox myRng.Address & vbLf & myRng.Rows.Count
End Sub

You'll see that the .rows.count returns the number of rows in the
first area--not the number of rows in the entire range.

Instead, you can look at a single column in that autofiltered range
and count the number of visible cells.

If that number is 1, then only the headers are visible (no real
data).

If you want the number of rows that are visible (not counting the
header), just subtract 1 from the quantity of visible cells in that
column.

Dim vRng as range
dim vRows as long
with worksheets("Sheet999").autofilter.range
set vrng = .columns(1).cells.specialcells(xlcelltypevisible)
vrows = vrng.cells.count - 1
select case vrows
case is = 0 'shouldn't happen in your case!
case is = 1
'what should happen here
case is = 2
'what should happen here
case is 2
msgbox "oh, oh!"
end select
end with

(Untested, uncompiled. Watch for typos.)


bst wrote:

i'm working on an inventory tracking sheet that has a snapshot of
data for the current week and the week before. it tracks changes
in inventory assignment. i have already built an array that has
each unique id for the inventory that is being tracked. i then
want to loop the array applying an autofilter on the sheet with
the criteria based on the array data. the result of the autofilter
(not counting the header) either 1 row (the item has been removed
from inventory) or two rows (the data is still in inventory, need
to check if it has been reassigned).

my problem is that i can't seem to test for how many rows resulted
in the autofilter.

what i have so far:
for xCtr = 1 to totalSerials
ActiveSheet.UsedRange.Select
Selection.AutoFilter field:=2, Criteria1:=uniqueSerials(xCtr)
Set filteredRange = selection.SpecialCells(xlCellTypeVisible)

if filteredRange.rows.count = 1 then
filteredRange.cells(2,colException).value = "x"
'row 2 because the first row is the header
end if
if filteredRange.rows.count = 2 then
if filteredRange.cells(2,colName).value < _
filteredRange.cells(3, colName).value then
filteredRange.cells(2, colException).value
= "x" filteredRange.cells(3,
colException).value = "x"
end if
end if
selection.autofilter
next xCtr

then i'll sort the sheet based on the exception column

the first time the macro is ran it works fine, afterwards
filteredRange.rows.count returns 1 everytime. i get the same
results with filteredRange.specialcells(xlCellTypeVisible).coun t
i have tried assigning filteredRange different ways as well with
the same results.

do i even need filteredRange? if not how should i proceed? if so,
how can i get it to work as desired? i've read about 10 pages of
google searching autofilter in the newsgroup and can't seem to
find what i need. copying the rows to another sheet would make the
macro run too long and is not desired since i want to sort the
results of the conditionals above.

TIA
bst