Microsoft Excel: Select a row containing a specific text with a Macro

The following code will select rows that contain a certain text in one of its cells

Sub SelectManyRows()
Dim CatchPhrase As String
Dim WholeRange As String
Dim AnyCell As Object
Dim RowsToSelect As String

CatchPhrase = “Text you are looking for”
‘first undo any current highlighting
Selection.SpecialCells(xlCellTypeLastCell).Select
WholeRange = “A1:” & ActiveCell.Address
Range(WholeRange).Select
On Error Resume Next ‘ ignore errors
For Each AnyCell In Selection
If InStr(UCase$(AnyCell.Text), UCase$(CatchPhrase)) Then
If RowsToSelect <> “” Then
RowsToSelect = RowsToSelect & “,” ‘ add group separator
End If
RowsToSelect = RowsToSelect & Trim$(Str$(AnyCell.Row)) & “:” & Trim$(Str$(AnyCell.Row))
End If
Next
On Error GoTo 0 ‘ clear error ‘trap’
Range(RowsToSelect).Select
End Sub

 

The following code will highlight rows that contain a certain text in one of its cells

Sub ShowRowsOfInterest()
Dim CatchPhrase As String
Dim AnyRange As String
Dim WholeRange As String
Dim AnyCell As Object

CatchPhrase = “Text you are looking for”
‘first undo any current highlighting
Selection.SpecialCells(xlCellTypeLastCell).Select
WholeRange = “A1:” & ActiveCell.Address
AnyRange = “1:” & Trim$(Str$(ActiveCell.Row))
Rows(AnyRange).Select
Selection.Interior.ColorIndex = xlNone
Range(WholeRange).Select
On Error Resume Next ‘ ignore errors
For Each AnyCell In Selection
If InStr(UCase$(AnyCell.Text), UCase$(CatchPhrase)) Then
AnyRange = Trim$(Str$(AnyCell.Row)) & “:” & Trim$(Str$(AnyCell.Row))
Rows(AnyRange).Interior.ColorIndex = 36 ‘ light yellow
End If
Next
On Error GoTo 0 ‘ clear error ‘trap’
Range(“A1”).Select

End Sub

By: 2kmaro