I think the problem is the code...

MainRange.AdvancedFilter(Action:=xlFilterInPlace, _
CriteriaRange:=ActiveCell)


....is not returning a range object (or any object for that matter). Though,
the filter does get applied to MainRange when you look at the worksheet.
Perhaps this is the way to go if I can then create range variables that
access the filtered columns of MainRange. Thanks for your help, this is
definitely an interesting path to pursue.

Jay


"Ryan H" wrote:

Rick,

Can he do it with AdvancedFilter(xlFilterInPlace)? If he filters it in
place can't he then take the visible rows? I tried to do it that way but I
kept getting an error saying "Object Required".

Sub FilterData()

Dim MainRange As Range
Dim lngLastRow As Long
Dim MyRange As Range

lngLastRow = Cells(Rows.Count, "B").End(xlUp).Row
Set MainRange = Range("B2:B" & lngLastRow)

Set MyRange = MainRange.AdvancedFilter(Action:=xlFilterInPlace, _
CriteriaRange:=ActiveCell)

MsgBox MyRange.Address

End Sub
--
Cheers,
Ryan


"Rick Rothstein" wrote:

Give this macro a try...

Sub SelectBfromA()
Dim x As Long, LastRow As Long, R As Range
Const ValueToFind = 2
LastRow = Cells(Rows.Count, "A").End(xlUp).Row
For x = 1 To LastRow
If Cells(x, "A").Value = ValueToFind Then
If R Is Nothing Then
Set R = Cells(x, "A")
Else
Set R = Union(R, Cells(x, "A"))
End If
End If
Next
R.Offset(0, 1).Select
End Sub

Just set the ValueToFind constant (in the Const statement) to the value you
want to find in Column A.

--
Rick (MVP - Excel)


"Jay" wrote in message
...
If I have the following range:

Row/Col A B
1 2 10
2 2 2
3 3 8
4 4 5
5 3 10
6 2 3
7 2 5
8 1 5
9 2 6
10 3 9

Suppose I have a function that is called by MyFunction(B1:B12). Is there
any way within the MyFunction routine to define a range of the numbers in
column B for which column A = 2. If I were doing a SumProduct, the idea
would by SumProduct(--(A1:A10=2),B1:B10). In this case, however, I just
want
a range that is the equivilent of (--(A1:A10=2),B1:B10). The new range
would
be include cells B1, B2, B6, B7 and B9. I was hoping to be able to do
this
without any kind of For/Next loop.

Thanks for your help.

.