Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Trouble with Filtered Ranges
I am pretty new to Excel Object Model. I need some help.
I have two worksheets that have two columns in both where data has to be matched and non-matching data have to be marked out in one of the sheets. Following is the code that I use to do the match : --------------------------------------------------------------------------- 'STEP-1 : Find MasterID in Parent set Set rng1 = sA.Range("A:A").Find(sB.Col1.cell(2,1), , , xlWhole) if not rng1 is Nothing then ' STEP-2 : Filter out all rows with matching MasterID from the Parent set sA.Range("A1:Q5000").AutoFilter( _ Field:=13, _ Criteria1:=sB.Col1.cell(2,1) , _ Visibledropdown:=False) set rng2 = sA.autofilter.range 'set rng2 = sA.autofilter.range.specialcells(xlVisible) ' Get the number of rows filtered intFilteredRowsCount = rng2.Columns(13).SpecialCells(xlVisible).Count-1 if not rng2 is nothing then ' STEP-3 : Find the ChildID in the filtered rows of Parent set. Set rng3 = rng2.Range("A1:A" & intFilteredRowsCount ).Find ( _ sB.Col3.Cells(2,3), _ , _ , _ xlWhole, xlByColumns, xlNext) end if end if I expected the "rng2" to have cells/rows only that met the filter criterion, but though the worksheet sA displays only the filtered rows, the find function in STEP-3 is searching the entire worksheet as if there was no filter. My queries: 1. Why is the rng2 visibly filtered, but not accessible as filtered set? 2. Why does the value of rng2.cell(R, C) correspond to the value of cell( R, C ) in used range of the worksheet despite the fact that rng2 represents a range which should hold a set of filtered rows? 3. How should I access the set of resulting data after I apply the autofilter method on a range? Could anyone throw some light on the above problem? Thanks, Rajesh |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Trouble with Pivot Tables & Named Ranges | Excel Worksheet Functions | |||
Trouble identifying filtered column | Excel Discussion (Misc queries) | |||
Count Filtered Visible Items that Match Numeric Criteria between two ranges | Excel Worksheet Functions | |||
Trouble with selecting multiple ranges of data | Excel Worksheet Functions | |||
Create my own Class Object for Ranges, having trouble assigning name property | Excel Programming |