Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
select entire rows using a contains comparison - an example
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Use find to select entire rows with a cell with that value | Excel Worksheet Functions | |||
Select the entire row | Excel Discussion (Misc queries) | |||
select cell rather selecting entire row | Excel Programming | |||
Using SELECT...CASE with comparison operators? | Excel Programming | |||
How to select entire column using VBA | Excel Programming |