Defining a range as a subset of cells in another range

[Jay]

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.

 

[Barb Reinhardt]

I think the only way you're going to be able to do this is with a For/Next
loop.

If you need assistance with that, come back.

 

[Rick Rothstein]

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.

 

[Ryan H]

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

 

[Jay]

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

 

[Jay]

Rick,

Thank you for this, the Union function is what I was looking for in terms of
creating a range. I have a follow up question for you. Suppose I ran your
macro twice creating two Ranges "R1" and "R2" that have the same number of
cells in each. I'm having a lot of trouble utilizing those two ranges in any
functions. For example,
SumProduct(R1, R2) won't work. Do you have any thoughts about how I could
get any of the WorkSheet functions to work using these two ranges?

 

[Jay]

I have some new logic in my code filtering the two ranges R1 and R2 such that
both ranges definitely have the same number of cells. I'm able to use R1 and
R2 separately in functions, for example
Application.WorksheetFunctions.Sum(R1) works fine and
Application.WorksheetFunctions.Sum(R2) works fine.
Application.WorksheetFunctions.SumProduct(R1, R2) crashes. Below is the code
I'm using below. "V" is the column of data being analyzed, the named range
"Code" contains the codes used for filtering the data. It has the same
number of rows as "V".

Sub SelectBfromA(V As Range)
Dim x As Long, LastRow As Long, R1 As Range
Dim y As Long, R2 As Range
Dim ValueToFind As Integer

ValueToFind = 35

For x = 1 To V.Rows.Count
If Worksheets("Data").Cells(x, Range("Code").Column).Value = ValueToFind
Then
If R1 Is Nothing Then
Set R1 = Worksheets("Data").Cells(x, V.Column)
Else
Set R1 = Union(R1, Worksheets("Data").Cells(x, V.Column))
End If
End If
Next


ValueToFind = 32

For y = 1 To V.Rows.Count
If Worksheets("Data").Cells(y, Range("Code").Column).Value = ValueToFind
Then
If R2 Is Nothing Then
Set R2 = Worksheets("Data").Cells(y, V.Column)
Else
Set R2 = Union(R2, Worksheets("Data").Cells(y, V.Column))
End If
End If
Next

'this confirm that the sum function works as expected on R1 and R2
MsgBox Application.WorksheetFunction.Sum(R1)
MsgBox Application.WorksheetFunction.Sum(R2)
'this confirm that R1 and R2 have the same number of cells
MsgBox R1.Cells.Count
MsgBox R2.Cells.Count
'this function crashes
MsgBox Application.WorksheetFunction.SumProduct(R1, R2)
End Sub

 

[Jay]

Some more complications...

In trying to use the ranges created from Union, I can't refer to the cells
properly.

If I try the following it works fine:

Dim c as range
For each c in R1.Cells
msgbox c.Address
Next

If I try the following the second iteration when I=2 gives me the address of
the row right below R1.Cells(1,1). It doesn't give me the address of the
second cell in R1:

Dim I As long
For I = 1 to R1.Cells.Count
msgbox R1.Cells(I, 1).Address
Next I

 

[Rick Rothstein]

The Cells property does not iterate cells in a range; rather, it references
the cell at the row and column specified.

 

[Rick Rothstein]

I think the SUMPRODUCT function requires contiguous ranges to iterate over
and I'm guessing that either R1 or R2 or both are non-contiguous.

 

[Rick Rothstein]

The AdvancedFilter is a method... it does not (as far as I know) return a
Range, it performs an action on the worksheet... I don't do much with
filtering myself, but my guess would be that you would then need to do
things (such as ShowAllData) to that worksheet that was filtered.

 

[Jay]

Rick,

Thanks for this answer and the previous one as well. Both makes sense, and
you are correct in your other response to me that the cells are not
contiguous.

I've come up with another way of accessing the cells in R1 and R2 using
R1.Areas. But won't this cause problems if any of the cells in R1 or R2 ARE
contiguous?

Do you know of a way to access the cells in the range, rather than the
column? How do I access the second cell in R1 for example?

 

[Rick Rothstein]

The Areas property is a collection of ranges of contiguous cells; so
Areas(1) references the first area of contiguous cells, Areas(2) references
the second area of contiguous cells and so on. You can iterate through the
Areas one at a time in a loop and then in a sub-loop iterate through the
cells in each Area. How you handle this depends on what you want your code
to do. One caveat with Areas... the areas within Areas iterate in the order
each area was added. So, if you, for example, select A1:C4 and E5:H9 in that
order, then Areas(1) would correspond too A1:C4 and Areas(2) would
correspond to E5:H9. HOWEVER, if you select these same cells in the order
E5:H9 and then A1:C4, then Areas(1) would correspond to E5:H9 and Areas(2)
would correspond to A1:C4. Because of this, there is no real way to answer
your last question... "How do I access the second cell in R1 for
example?"... the second cell, whatever that might mean (see my next
comments) would be dependent on the order the areas were created in.
HOWEVER, there is no real meaning to a "second cell" within a non-contiguous
range. Consider this range... C1:E4,A6:C10 (select it on the worksheet so
you can better see the arrangement)... which would you consider to be the
second cell in the range... D1, C2, B6 or A7?