How to Use VBA to Select Rows that Match the Active Cell in Excel

VBA is a programming language that allows you to automate tasks and manipulate data in Excel. One of the common tasks you may want to do is to select rows that match a certain value or condition in the current active cell. For example, if you have a table of data with different categories, and you want to select all the rows that belong to the same category as the active cell, you can use VBA to do that.

There are different ways to achieve this goal, but one of the simplest and most efficient ways is to use the Union method. The Union method takes two or more ranges as arguments and returns a new range that includes all the cells from those ranges. You can use this method to combine the rows that match the active cell into one range, and then select that range.

To use the Union method, you need to loop through the cells in the table and compare them with the active cell. If they match, you can add them to the union range using the & operator. You also need to specify the entire row of each matching cell using the EntireRow property. After the loop, you can select the union range using the Select method.

Procedures

Here are the steps to create a VBA macro that can select rows that match the current active cell:

1. Open the Visual Basic Editor (VBE) by pressing Alt + F11 on your keyboard.
2. Insert a new module by clicking Insert > Module on the menu bar.
3. In the module window, type or paste the following code:

Sub SelectMatchingRows()
    Dim tableR As Range, cell As Range, unionR As Range
    Dim activeValue As Variant
    
    'Set the table range to the range you want to search
    Set tableR = Range("A1:C10")
    
    'Get the value of the active cell
    activeValue = ActiveCell.Value
    
    'Loop through the cells in the table range
    For Each cell In tableR
        'If the cell value matches the active value
        If cell.Value = activeValue Then
            'If the union range is empty, set it to the entire row of the cell
            If unionR Is Nothing Then
                Set unionR = cell.EntireRow
            'Otherwise, add the entire row of the cell to the union range
            Else
                Set unionR = Union(unionR, cell.EntireRow)
            End If
        End If
    Next cell
    
    'Select the union range
    unionR.Select
End Sub

4. Save the module and return to the Excel workbook.
5. Select a cell in the table that contains the value you want to match.
6. Run the macro by pressing F5 on your keyboard or clicking Run > Run Sub/UserForm on the menu bar.
7. The macro will select all the rows that match the value of the active cell.

Example

To illustrate how the macro works, let’s use the following table as an example:

Suppose we want to select all the rows that have the category A. We can do the following:

1. Select any cell in the column A that has the value A, such as A1.
2. Run the macro by pressing F5 or clicking Run > Run Sub/UserForm.
3. The macro will select all the rows that have the category A, as shown below:

Other approaches

There are other ways to select rows that match the current active cell using VBA, such as using the AutoFilter method, the Find method, or the AdvancedFilter method. However, these methods may have some limitations or drawbacks, such as requiring a header row, changing the original data, or being more complex. The Union method is simple, fast, and flexible, and does not alter the data. Therefore, it is recommended to use the Union method for this task.