How to Select Rows With Specific Text in Excel VBA

Microsoft Excel provides a powerful platform for analyzing data, and sometimes, you may need to work with rows that contain specific text. Automating this process with VBA can save you time and effort, especially when dealing with large datasets. This tutorial will guide you through selecting rows in Excel that contain specified text using VBA.

Example

Steps to Select Rows with Specific Text Using VBA

To select rows based on specific text in Excel using VBA, follow these steps:

Step 1: Define the Range and the Text to Search For

Identify the worksheet and the range of cells you want to search. Also, define the text you’re looking for. In our example, we’ll search for the text “SpecificText”.

Step 2: Loop Through Each Row in the Range

Use a For loop to iterate through each row in the specified range.

Step 3: Check if the Row Contains the Specific Text

During the loop, use the InStr function to check if the specific text is present in any cell of the row.

Step 4: Select the Row if the Text is Found

If the text is found within a row, use the Select method to select the entire row.

Example VBA Code

Here’s the full code that you can use as a reference to select rows with specific text:

Sub SelectRowsWithSpecificText()
    Dim ws As Worksheet
    Set ws = ThisWorkbook.Sheets("Sheet1")
    Dim searchRange As Range
    Set searchRange = ws.Range("A1:A100")
    Dim cell As Range
    Dim searchText As String
    searchText = "SpecificText"
    
    Dim selectedRange As Range ' Create a Range object to store all matching rows
    
    For Each cell In searchRange
        If InStr(1, cell.Value, searchText) > 0 Then
            If selectedRange Is Nothing Then
                ' Initialize the selectedRange with the first matching row
                Set selectedRange = cell.EntireRow
            Else
                ' Add subsequent matching rows to the selectedRange
                Set selectedRange = Union(selectedRange, cell.EntireRow)
            End If
        End If
    Next cell
    
    ' Select all the matching rows at once
    If Not selectedRange Is Nothing Then
        selectedRange.Select
    End If
End Sub

Sub SelectRowsWithSpecificText()

Dim ws As Worksheet

Set ws = ThisWorkbook.Sheets("Sheet1")

Dim searchRange As Range

Set searchRange = ws.Range("A1:A100")

Dim cell As Range

Dim searchText As String

searchText = "SpecificText"

Dim selectedRange As Range ' Create a Range object to store all matching rows

For Each cell In searchRange

If InStr(1, cell.Value, searchText) > 0 Then

If selectedRange Is Nothing Then

' Initialize the selectedRange with the first matching row

Set selectedRange = cell.EntireRow

Else

' Add subsequent matching rows to the selectedRange

Set selectedRange = Union(selectedRange, cell.EntireRow)

End If

Next cell

' Select all the matching rows at once

If Not selectedRange Is Nothing Then

selectedRange.Select

End If

End Sub

When executing this script, replace “Sheet1” with the name of your worksheet and adjust the search range (“A1:A100”) to your desired range. Also, replace “SpecificText” with the actual text you’re looking for.

Result

Conclusion

In this tutorial, you’ve learned how to write a VBA macro to select rows with specific text in Excel. This method is particularly useful when dealing with large datasets where manual searching and selection are impractical. You can modify the provided code for different scenarios, adjusting the range or search criteria as needed.

Remember, VBA can extend the functionality of your Excel workbooks, automating repetitive tasks and increasing your productivity. Excel VBA is a robust tool, but care should be taken when using it to ensure you are manipulating data as intended.

Tomasz Decker is an Excel specialist, skilled in data analysis and financial modeling.

Post Views: 13