Select rows which satisfy 2 criteria

C

Colin Hayes

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.
 
V

Vacuum Sealed

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.
Click to expand...
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.
 
D

Don Guillett

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.
Click to expand...

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

Colin Hayes

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
Click to expand...

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
 
V

Vacuum Sealed

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

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
 
C

Colin Hayes

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
Click to expand...

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
 
D

Don Guillett

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
Click to expand...

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
Click to expand...

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

Vacuum Sealed

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
Click to expand...

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
Click to expand...

What may be a nightmare to you may/?? not be to some.
Click to expand...
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.
 
G

GS

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
Click to expand...

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
Click to expand...

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
 
G

GS

Mick, some suggested revisions...
Sub mySelection()
Click to expand...
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
Click to expand...
Set vCell = Nothing: Set cCell = Nothing
End Sub
Click to expand...

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
 
V

Vacuum Sealed

Mick, some suggested revisions...
Sub mySelection()
Click to expand...
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
Click to expand...
Set vCell = Nothing: Set cCell = Nothing
End Sub
Click to expand...

HTH: Though the loop does nothing with its selection, it demos stepping
through a range one cell at a time.
Click to expand...
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.
 
G

Gord Dibben

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
 
C

Colin Hayes

Well said<bg>
Click to expand...


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
 
G

GS

Colin Hayes brought next idea :
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
Click to expand...

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
 
G

Gord Dibben

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

complex sort search and replace macro 2
Match and delete rows on two different sheets from reference numbers 4
Find matching records in two tables using mulitiple criteria 1
Popups to look up on two variable columns 1
Macro - How to not copy blank criteria? 1
change all below or equal to an input number 4
Sumif criteria between two dates 3
Counting number of rows based on mutiple criteria 2

Top