select entire rows using a contains comparison - an example

[aztecbrainsurgeon]

No question here, just an example procedure for the archive.

Select entire rows based on a selection using a "contains"
comparision operation.


Sub SelectRowsByContains()

'Evaluates for a prompted value to see if the value is
'contained in any of the cells in selection,
'If value is present in the cell, the whole row for that cell or cells
is selected.

Dim cell, ContainsRange As Range
On Error Resume Next
EvalValue = Application.InputBox(prompt:="Enter the value you wish
to evaluate for in each cell in selection. This value will be evaluated
to see if it is contained in each cell.", Title:="Select Rows Based on
a Single Column", _
Default:="yourvaluehere", Type:=1
+ 2) 'type 1 is number

If EvalValue = False Then Exit Sub

For Each cell In Selection

''******** Case-sensitive evaluation ***********

If cell.Value Like "*" & EvalValue & "*" = True Then

If ContainsRange Is Nothing Then
Set ContainsRange = cell
Else
Set ContainsRange = Union(cell, ContainsRange)
End If

End If


''******** No case-sensitive evaluation ***********

' If UCase(cell.Value) Like "*" & UCase(EvalValue) & "*"
= True Then
'
' If ContainsRange Is Nothing Then
' Set ContainsRange = cell
' Else
' Set ContainsRange = Union(cell, ContainsRange)
' End If
'
' End If

Next cell

'Test for a valid range result:
If Not ContainsRange Is Nothing Then
ContainsRange.Select

Selection.EntireRow.Select ' Turn this line on/off for whole
row selection preference
Else
MsgBox "The value you entered was not contained in any cell in
the selection"

End If


End Sub

select rows based on a selection using a contains function
contains function comparision contains comparison