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.
Excel Productivity Tools
Office Tab: Bring powerful tabs to Office (include Excel), just like Chrome, Safari, Firefox and Internet Explorer. Save you half the time, and reduce thousands of mouse clicks for you. 30-day Unlimited Free Trial
Kutools for Excel: Save 70% of your time and solve 80% Excel problems for you. 300+ advanced features designed for 1500+ work scenario, make Excel much easy and increase productivity immediately.60-day Unlimited Free Trial
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.