How to select range based on cell value in another column in Excel?

In this article, I will talk about how to select ranges based on cell value in another column. Take an example, if the cells display FALSE in column C, the relative cells in column A and column B will be selected as below screenshot shown. There is no built-in feature that can help you to solve this task, but in this article, I can handle it with a VBA code.

Select range based on cell value in another column by VBA code

---

Select range based on cell value in another column by VBA code

Select range based on cell value in another column by VBA code

1. Enable the sheet you want to use, and press Alt + F11 keys to enable the Microsoft Visual Basic for Applications window.

2. Click Insert > Module, and then paste below code to the new Module window.

VBA: Select range based on cell value

Sub SelectByCellValue()
'UpdatebyExtendoffice20161128
    Dim lastrow As Long
    Dim xRg As Range, yRg As Range
    'change Sheet1 to suit
    With ThisWorkbook.Worksheets("Sheet2")
        lastrow = .Cells(.Rows.Count, "C").End(xlUp).Row
        Application.ScreenUpdating = False
        For Each xRg In .Range("C1:C" & lastrow)
            If UCase(xRg.Text) = "FALSE" Then
                If yRg Is Nothing Then
                    Set yRg = .Range("A" & xRg.Row).Resize(, 2)
                Else
                    Set yRg = Union(yRg, .Range("A" & xRg.Row).Resize(, 2))
                End If
            End If
        Next xRg
        Application.ScreenUpdating = True
    End With

    If Not yRg Is Nothing Then yRg.Select
End Sub

3. Press F5 key to run the VBA, and then the ranges have been selected based on the values in column C.

Note: in the VBA script, Sheet2 is the active sheet name, C indicate the column you want to select based on, the A and 2 in ("A" & xRg.Row).Resize(, 2) indicates that to select two columns from column A. You can change the references as you need.

Tip.If you want to quickly select cells which are equal to a specific value, please try to use the Kutools for Excel’s Select Specific Cells utility as shown in the following screenshot. It’s full function without limitation in 30 days, please download and have a free trial now.

---