September 4

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


Copyright 2021. All rights reserved.

Posted September 4, 2013 by Timothy Conrad in category "Software

About the Author

If I were to describe myself with one word it would be, creative. I am interested in almost everything which keeps me rather busy. Here you will find some of my technical musings. Securely email me using - PGP: 4CB8 91EB 0C0A A530 3BE9 6D76 B076 96F1 6135 0A1B