Select rows which satisfy 2 criteria
 

HI

I need to select rows which satisfy criteria contained in 2 different
columns.

For example , select those rows which have cells in column A with a
number below 200 and also have the word 'Green' in column F.

The cells should be selected rather than filtered if possible.


Grateful any help.

Select rows which satisfy 2 criteria
 

On 18/03/2012 1:32 AM, Colin Hayes wrote:

HI

I need to select rows which satisfy criteria contained in 2 different
columns.

For example , select those rows which have cells in column A with a
number below 200 and also have the word 'Green' in column F.

The cells should be selected rather than filtered if possible.


Grateful any help.

G'day Colin

this code will do what you want, it will also highlight those rows where
your criteria is met.

This will highlight the entire row (Light Green) for each row that matches.

Sub mySelection()
Dim myRng As Range
Dim c As Range
Set myRng = Range("A1:A10")
For Each c In myRng
If c < 200 And c.Offset(0, 5).Value = "Green" Then
With c
.EntireRow.Interior.Color = 5296274
End With
End If
Next c
End Sub

And this will only color those cell within a specified range, change the
( With c.Resize(1, 6) range to suit your needs e.g (1, 10).

Sub mySelection()
Dim myRng As Range
Dim c As Range
Set myRng = Range("A1:A10")
For Each c In myRng
If c < 200 And c.Offset(0, 5).Value = "Green" Then
With c.Resize(1, 6)
.Interior.Color = 5296274
End With
End If
Next c
End Sub

HTH
Mick.

Select rows which satisfy 2 criteria
 

On Saturday, March 17, 2012 9:32:24 AM UTC-5, Colin Hayes wrote:
HI

I need to select rows which satisfy criteria contained in 2 different
columns.

For example , select those rows which have cells in column A with a
number below 200 and also have the word 'Green' in column F.

The cells should be selected rather than filtered if possible.


Grateful any help.

It is much better and faster to filter and then act of the
specialcells(xlvisible)
than it is to loop.....

Select rows which satisfy 2 criteria
 

In article . com,
Vacuum Sealed writes
G'day Colin

this code will do what you want, it will also highlight those rows where your criteria
is met.

This will highlight the entire row (Light Green) for each row that matches.

Sub mySelection()
Dim myRng As Range
Dim c As Range
Set myRng = Range("A1:A10")
For Each c In myRng
If c < 200 And c.Offset(0, 5).Value = "Green" Then
With c
.EntireRow.Interior.Color = 5296274
End With
End If
Next c
End Sub

Hi Mick

OK thanks for that. It works fine.

Would it be an easy thing to extend the code so that the two columns and
the two values could be entered as variables via popup? This would save
re-writing the code if the parameters change.

Also , because of this , the concerned rows would just need simple
selection rather than colour-coding I would think.

Grateful for your advice on this.



Best Wishes

Select rows which satisfy 2 criteria
 

On 18/03/2012 4:21 AM, Don Guillett wrote:
On Saturday, March 17, 2012 9:32:24 AM UTC-5, Colin Hayes wrote:
HI

I need to select rows which satisfy criteria contained in 2 different
columns.

For example , select those rows which have cells in column A with a
number below 200 and also have the word 'Green' in column F.

The cells should be selected rather than filtered if possible.


Grateful any help.

It is much better and faster to filter and then act of the
specialcells(xlvisible)
than it is to loop.....


Colin

I tend to agree with Don, but if you're happy to continue with your
requirement then you will need to adopt "Helper Cells" so that your
Userform/User input can assign the values to. I the following example, I
have used [K1] for your Value & [M1] for your Color, you can change them
to whatever you want so long as you change the code to match.

I am not proficient enough in Excel as to assist you with creating a
UserForm/Input Form for passing the parameter input though.

HTH
Mick.

Sub mySelection()
Dim myRng As Range
Dim vCell As Range, cCell As Range
Dim c As Range
Set myRng = Range("A1:A10")
Set vCell = [K1]
Set cCell = [M1]
For Each c In myRng
If c < vCell And c.Offset(0, 5).Value = cCell Then
With c
.EntireRow.Select
End With
End If
Next c
End Sub

Select rows which satisfy 2 criteria
 

Colin

I tend to agree with Don, but if you're happy to continue with your
requirement then you will need to adopt "Helper Cells" so that your
Userform/User input can assign the values to. I the following example, I
have used [K1] for your Value & [M1] for your Color, you can change them
to whatever you want so long as you change the code to match.

I am not proficient enough in Excel as to assist you with creating a
UserForm/Input Form for passing the parameter input though.

HTH
Mick.

Sub mySelection()
Dim myRng As Range
Dim vCell As Range, cCell As Range
Dim c As Range
Set myRng = Range("A1:A10")
Set vCell = [K1]
Set cCell = [M1]
For Each c In myRng
If c < vCell And c.Offset(0, 5).Value = cCell Then
With c
.EntireRow.Select
End With
End If
Next c
End Sub


Hi Mick

OK Thanks for that.

The problem I have is that I want to run an existing macro on the
results produced by this procedure. This depends on cells being selected
rather than filtered. If I were starting from scratch I would adopt the
method that Don has explained so well. Unfortunately though I'm having
to fit this into an exiting framework which wouldn't work on filtered
results , and would be a nightmare to unpick.

Thanks anyway for your time.



Best Wishes

Select rows which satisfy 2 criteria
 

On Sunday, March 18, 2012 9:15:16 AM UTC-5, Colin Hayes wrote:
Colin

I tend to agree with Don, but if you're happy to continue with your
requirement then you will need to adopt "Helper Cells" so that your
Userform/User input can assign the values to. I the following example, I
have used [K1] for your Value & [M1] for your Color, you can change them
to whatever you want so long as you change the code to match.

I am not proficient enough in Excel as to assist you with creating a
UserForm/Input Form for passing the parameter input though.

HTH
Mick.

Sub mySelection()
Dim myRng As Range
Dim vCell As Range, cCell As Range
Dim c As Range
Set myRng = Range("A1:A10")
Set vCell = [K1]
Set cCell = [M1]
For Each c In myRng
If c < vCell And c.Offset(0, 5).Value = cCell Then
With c
.EntireRow.Select
End With
End If
Next c
End Sub


Hi Mick

OK Thanks for that.

The problem I have is that I want to run an existing macro on the
results produced by this procedure. This depends on cells being selected
rather than filtered. If I were starting from scratch I would adopt the
method that Don has explained so well. Unfortunately though I'm having
to fit this into an exiting framework which wouldn't work on filtered
results , and would be a nightmare to unpick.

Thanks anyway for your time.



Best Wishes

What may be a nightmare to you may/?? not be to some.

Select rows which satisfy 2 criteria
 

On 19/03/2012 9:09 AM, Don Guillett wrote:
On Sunday, March 18, 2012 9:15:16 AM UTC-5, Colin Hayes wrote:
Colin

I tend to agree with Don, but if you're happy to continue with your
requirement then you will need to adopt "Helper Cells" so that your
Userform/User input can assign the values to. I the following example, I
have used [K1] for your Value& [M1] for your Color, you can change them
to whatever you want so long as you change the code to match.

I am not proficient enough in Excel as to assist you with creating a
UserForm/Input Form for passing the parameter input though.

HTH
Mick.

Sub mySelection()
Dim myRng As Range
Dim vCell As Range, cCell As Range
Dim c As Range
Set myRng = Range("A1:A10")
Set vCell = [K1]
Set cCell = [M1]
For Each c In myRng
If c< vCell And c.Offset(0, 5).Value = cCell Then
With c
.EntireRow.Select
End With
End If
Next c
End Sub


Hi Mick

OK Thanks for that.

The problem I have is that I want to run an existing macro on the
results produced by this procedure. This depends on cells being selected
rather than filtered. If I were starting from scratch I would adopt the
method that Don has explained so well. Unfortunately though I'm having
to fit this into an exiting framework which wouldn't work on filtered
results , and would be a nightmare to unpick.

Thanks anyway for your time.



Best Wishes

What may be a nightmare to you may/?? not be to some.
Colin

As I, and doubtless many contributors here know it can be daunting when
you inherit older hand-me-down workbooks and their problems as one
person's logic and the way they structure may not translate the same,
that said! As Don has intimated, by having an outsiders perspective may
indeed prove not so much a nightmare as you think.

Many of the long-term contributors here astound and amaze me with their
insight and knowledge, of which they give of so freely, I frequently
require help and turn to this NG for assistance, and in turn I give back
what little I have to offer also.

Although the task for you may appear overwhelming and nightmarish,
sharing the entire problem here may bring about a best case outcome as
opposed to placing band-aids here & there.

Give it some serious thought.

Cheers
Mick.

Select rows which satisfy 2 criteria
 

Colin Hayes laid this down on his screen :
Colin

I tend to agree with Don, but if you're happy to continue with your
requirement then you will need to adopt "Helper Cells" so that your
Userform/User input can assign the values to. I the following example, I
have used [K1] for your Value & [M1] for your Color, you can change them
to whatever you want so long as you change the code to match.

I am not proficient enough in Excel as to assist you with creating a
UserForm/Input Form for passing the parameter input though.

HTH
Mick.

Sub mySelection()
Dim myRng As Range
Dim vCell As Range, cCell As Range
Dim c As Range
Set myRng = Range("A1:A10")
Set vCell = [K1]
Set cCell = [M1]
For Each c In myRng
If c < vCell And c.Offset(0, 5).Value = cCell Then
With c
.EntireRow.Select
End With
End If
Next c
End Sub


Hi Mick

OK Thanks for that.

The problem I have is that I want to run an existing macro on the results
produced by this procedure. This depends on cells being selected rather than
filtered. If I were starting from scratch I would adopt the method that Don
has explained so well. Unfortunately though I'm having to fit this into an
exiting framework which wouldn't work on filtered results , and would be a
nightmare to unpick.

Thanks anyway for your time.



Best Wishes

The results return a 'Selection' in the form of an entire row. In this
case you could pass a ref to the selected row to the macro you want to
run on it. If there's multiple rows that qualify then you could built a
delimited string of their respective row nums and pass that to your
macro. So for example, modifying Mick's code sample, you could do
something like...

Sub MyMacro
Dim sRefs As String, rng As Range
Dim vCell As Range, cCell As Range
Set vCell = [K1]: Set cCell = [M1]

For Each rng in Range("A1:A10")
If rng < vCell And rng.Offset(, 5).Value = cCell Then _
sRefs = sRefs & "," & CStr(rng.Row)
Next 'rng
Call MyOtherMacro(Mid$(sRefs, 2)) 'trim leading comma beforehand

'Cleanup
Set vCell = Nothing: Set cCell = Nothing
End Sub

Sub MyOtherMacro(ByVal RowRefs As String)
Dim v As Variant
For Each v In Split(RowRefs, ",")
With Rows(v)
'do stuff
End With 'Rows(v)
Next 'v
End Sub

--
Garry

Free usenet access at http://www.eternal-september.org
ClassicVB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

Select rows which satisfy 2 criteria
 

Mick, some suggested revisions...

Sub mySelection()
Dim vCell As Range, cCell As Range, c As Range
Set vCell = [K1]: Set cCell = [M1]
For Each c In Range("A1:A10")
If c < vCell And c.Offset(0, 5).Value = cCell Then _
c.EntireRow.Select
Next c
Set vCell = Nothing: Set cCell = Nothing
End Sub

HTH: Though the loop does nothing with its selection, it demos stepping
through a range one cell at a time.

--
Garry

Free usenet access at http://www.eternal-september.org
ClassicVB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

Select rows which satisfy 2 criteria
 

On 19/03/2012 12:26 PM, GS wrote:
Mick, some suggested revisions...

Sub mySelection()
Dim vCell As Range, cCell As Range, c As Range
Set vCell = [K1]: Set cCell = [M1]
For Each c In Range("A1:A10")
If c < vCell And c.Offset(0, 5).Value = cCell Then _
c.EntireRow.Select
Next c
Set vCell = Nothing: Set cCell = Nothing
End Sub

HTH: Though the loop does nothing with its selection, it demos stepping
through a range one cell at a time.

As Always Garry

Evey day here is a learning day!

If only my primary and secondary schooling was more about the learning
and less about impressing and trying to pick up chicklettes..lol...:)

Cheers
Mick.

Select rows which satisfy 2 criteria
 

Don't beat yourself up Mick.

VBA was probably not on the curriculum so even if you paid
attention.......who does?..........you would not have seen it.

Besides, chicklettes are much more entertaining<g


Gord

On Mon, 19 Mar 2012 14:31:42 +1100, Vacuum Sealed
wrote:

Evey day here is a learning day!

If only my primary and secondary schooling was more about the learning
and less about impressing and trying to pick up chicklettes..lol...:)

Cheers
Mick.

Select rows which satisfy 2 criteria
 

Well said<bg

--
Garry

Free usenet access at http://www.eternal-september.org
ClassicVB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

Select rows which satisfy 2 criteria
 

In article , GS writes
Well said<bg



Hi Guys

I'm grateful for the responses to my original question. I do understand
the desire for coding in the most efficient and intelligent way but my
emphasis I have to say was on outcome rather than process.

Nevertheless , I still need in a routine that selects rather than
filters if anyone can advise. In the meantime , I'll try to update the
various macros that I have to use so that they are more efficient.

Thanks again

Best Wishes

Select rows which satisfy 2 criteria
 

Colin Hayes brought next idea :
In article , GS writes
Well said<bg



Hi Guys

I'm grateful for the responses to my original question. I do understand the
desire for coding in the most efficient and intelligent way but my emphasis I
have to say was on outcome rather than process.

Nevertheless , I still need in a routine that selects rather than filters if
anyone can advise. In the meantime , I'll try to update the various macros
that I have to use so that they are more efficient.

Thanks again

Best Wishes

Mick's macro selects the entire row (see my revised version of his
code), which you could process one at a time by passing a ref to the
selected row into your other macro.

My code 'collects' all the rows into a string and passes that into the
other macro so all rows with your criteria get processed one after the
other.

I'm not sure why you need to select anything (not enough details
provided), but that's a really inefficient approach in general!

--
Garry

Free usenet access at http://www.eternal-september.org
ClassicVB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

Select rows which satisfy 2 criteria
 

Colin

If you must select try this macro.

Sub Select_Rows()
Dim c As Range, tempR As Range
Set myRng = Range("A1:A100")
'check each cell in the selection
For Each c In myRng
If c < 200 And c.Offset(0, 5).Value = "Green" Then
If tempR Is Nothing Then
'initialize tempR with the first qualifying cell
Set tempR = c
Else
'add additional cells to tempR
Set tempR = Union(tempR, c)
End If
End If
Next c
'display message and stop if no cells found
If tempR Is Nothing Then
MsgBox "There are no Rows " & _
"that meet the criteria."
End
End If
'select qualifying cells
tempR.EntireRow.Select
End Sub


Gord

On Mon, 19 Mar 2012 15:24:56 +0000, Colin Hayes
wrote:

In article , GS writes
Well said<bg



Hi Guys

I'm grateful for the responses to my original question. I do understand
the desire for coding in the most efficient and intelligent way but my
emphasis I have to say was on outcome rather than process.

Nevertheless , I still need in a routine that selects rather than
filters if anyone can advise. In the meantime , I'll try to update the
various macros that I have to use so that they are more efficient.

Thanks again

Best Wishes

Select rows which satisfy 2 criteria
 

Hi All

OK thanks to everyone for their help. Much appreciated.



Best Wishes


In article , Gord Dibben
writes
Colin

If you must select try this macro.

Sub Select_Rows()
Dim c As Range, tempR As Range
Set myRng = Range("A1:A100")
'check each cell in the selection
For Each c In myRng
If c < 200 And c.Offset(0, 5).Value = "Green" Then
If tempR Is Nothing Then
'initialize tempR with the first qualifying cell
Set tempR = c
Else
'add additional cells to tempR
Set tempR = Union(tempR, c)
End If
End If
Next c
'display message and stop if no cells found
If tempR Is Nothing Then
MsgBox "There are no Rows " & _
"that meet the criteria."
End
End If
'select qualifying cells
tempR.EntireRow.Select
End Sub


Gord