Selecting data for certain conditions.
 

I have data arranged in 60 columns with multiple rows of data. How do
you select certain rows based on data criteria in the columns?

For example:

Report the rows with columns 1 - 16 where data in column X is 20,
column Y is < .25, column Z 15.5, etc., etc.


--
Jim15
------------------------------------------------------------------------
Jim15's Profile: http://www.excelforum.com/member.php...o&userid=26300
View this thread: http://www.excelforum.com/showthread...hreadid=400774

Selecting data for certain conditions.
 

Look under the data menu at Data=filters. Then use Autofilter or Advanced
filter.

--
Regards,
Tom Ogilvy


"Jim15" wrote in
message ...

I have data arranged in 60 columns with multiple rows of data. How do
you select certain rows based on data criteria in the columns?

For example:

Report the rows with columns 1 - 16 where data in column X is 20,
column Y is < .25, column Z 15.5, etc., etc.


--
Jim15
------------------------------------------------------------------------
Jim15's Profile:
http://www.excelforum.com/member.php...o&userid=26300
View this thread: http://www.excelforum.com/showthread...hreadid=400774

Selecting data for certain conditions.
 

If I follow correctly the "60" columns you mentioned is not directly
relevant, only the extent of rows to look in and the individual columns, say
X, Y, Z .

Sub test()
Dim b As Boolean
Dim rRws As Range
Dim rLook As Range
Dim nFirst As Long, nLast As Long
Dim v
Dim vCols

nFirst = 2 ' first row to look in
nLast = 40
vCols = Array("X", "Y", "Z")

For i = nFirst To nLast
b = False
For Each v In vCols
If Cells(i, v).Value 20 Then
b = True
Exit For
End If
If Cells(i, v).Value < 0.25 Then
b = True
Exit For
End If
If Cells(i, v).Value 15.5 Then
b = True
Exit For
End If
Next
If b Then
If rRws Is Nothing Then
Set rRws = Range(Cells(i, 1), Cells(i, 16))
Else
Set rRws = Union(rRws, Range(Cells(i, 1), Cells(i, 16)))
End If
End If
Next

If Not rRws Is Nothing Then
rRws.Select
Else
MsgBox "Nothing matched"
End If

End Sub

Report the rows with columns 1 - 16

I took that to mean select only part rows, between columns 1 - 16

If you have 000's of rows with the potential to create a union of more than
a few hundred discontiguous areas, this will become slow. If that's a
possibility break down into smaller sections, process each separately.

Regards,
Peter T


"Jim15" wrote in
message ...

I have data arranged in 60 columns with multiple rows of data. How do
you select certain rows based on data criteria in the columns?

For example:

Report the rows with columns 1 - 16 where data in column X is 20,
column Y is < .25, column Z 15.5, etc., etc.


--
Jim15
------------------------------------------------------------------------
Jim15's Profile:
http://www.excelforum.com/member.php...o&userid=26300
View this thread: http://www.excelforum.com/showthread...hreadid=400774

Selecting data for certain conditions.
 

Or use an advanced filter which should be more efficient or even could be
done without code.

--
Regards,
Tom Ogilvy

"Peter T" <peter_t@discussions wrote in message
...
If I follow correctly the "60" columns you mentioned is not directly
relevant, only the extent of rows to look in and the individual columns,
say
X, Y, Z .

Sub test()
Dim b As Boolean
Dim rRws As Range
Dim rLook As Range
Dim nFirst As Long, nLast As Long
Dim v
Dim vCols

nFirst = 2 ' first row to look in
nLast = 40
vCols = Array("X", "Y", "Z")

For i = nFirst To nLast
b = False
For Each v In vCols
If Cells(i, v).Value 20 Then
b = True
Exit For
End If
If Cells(i, v).Value < 0.25 Then
b = True
Exit For
End If
If Cells(i, v).Value 15.5 Then
b = True
Exit For
End If
Next
If b Then
If rRws Is Nothing Then
Set rRws = Range(Cells(i, 1), Cells(i, 16))
Else
Set rRws = Union(rRws, Range(Cells(i, 1), Cells(i, 16)))
End If
End If
Next

If Not rRws Is Nothing Then
rRws.Select
Else
MsgBox "Nothing matched"
End If

End Sub

Report the rows with columns 1 - 16

I took that to mean select only part rows, between columns 1 - 16

If you have 000's of rows with the potential to create a union of more
than
a few hundred discontiguous areas, this will become slow. If that's a
possibility break down into smaller sections, process each separately.

Regards,
Peter T


"Jim15" wrote in
message ...

I have data arranged in 60 columns with multiple rows of data. How do
you select certain rows based on data criteria in the columns?

For example:

Report the rows with columns 1 - 16 where data in column X is 20,
column Y is < .25, column Z 15.5, etc., etc.


--
Jim15
------------------------------------------------------------------------
Jim15's Profile:
http://www.excelforum.com/member.php...o&userid=26300
View this thread:
http://www.excelforum.com/showthread...hreadid=400774

Selecting data for certain conditions.
 

Indeed, that would be probably be a much better approach. Possibly even the
easier autofilter, as you also suggested previously, with a bit of a nudge.

Regards,
Peter T

"Tom Ogilvy" wrote in message
...
Or use an advanced filter which should be more efficient or even could be
done without code.

--
Regards,
Tom Ogilvy

"Peter T" <peter_t@discussions wrote in message
...
If I follow correctly the "60" columns you mentioned is not directly
relevant, only the extent of rows to look in and the individual columns,
say
X, Y, Z .

Sub test()
Dim b As Boolean
Dim rRws As Range
Dim rLook As Range
Dim nFirst As Long, nLast As Long
Dim v
Dim vCols

nFirst = 2 ' first row to look in
nLast = 40
vCols = Array("X", "Y", "Z")

For i = nFirst To nLast
b = False
For Each v In vCols
If Cells(i, v).Value 20 Then
b = True
Exit For
End If
If Cells(i, v).Value < 0.25 Then
b = True
Exit For
End If
If Cells(i, v).Value 15.5 Then
b = True
Exit For
End If
Next
If b Then
If rRws Is Nothing Then
Set rRws = Range(Cells(i, 1), Cells(i, 16))
Else
Set rRws = Union(rRws, Range(Cells(i, 1), Cells(i, 16)))
End If
End If
Next

If Not rRws Is Nothing Then
rRws.Select
Else
MsgBox "Nothing matched"
End If

End Sub

Report the rows with columns 1 - 16

I took that to mean select only part rows, between columns 1 - 16

If you have 000's of rows with the potential to create a union of more
than
a few hundred discontiguous areas, this will become slow. If that's a
possibility break down into smaller sections, process each separately.

Regards,
Peter T


"Jim15" wrote in
message ...

I have data arranged in 60 columns with multiple rows of data. How do
you select certain rows based on data criteria in the columns?

For example:

Report the rows with columns 1 - 16 where data in column X is 20,
column Y is < .25, column Z 15.5, etc., etc.


--
Jim15

------------------------------------------------------------------------
Jim15's Profile:
http://www.excelforum.com/member.php...o&userid=26300
View this thread:
http://www.excelforum.com/showthread...hreadid=400774