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.
- Reuse Anything: Add the most used or complex formulas, charts and anything else to your favorites, and quickly reuse them in the future.
- More than 20 text features: Extract Number from Text String; Extract or Remove Part of Texts; Convert Numbers and Currencies to English Words.
- Merge Tools: Multiple Workbooks and Sheets into One; Merge Multiple Cells/Rows/Columns Without Losing Data; Merge Duplicate Rows and Sum.
- Split Tools: Split Data into Multiple Sheets Based on Value; One Workbook to Multiple Excel, PDF or CSV Files; One Column to Multiple Columns.
- Paste Skipping Hidden/Filtered Rows; Count And Sum by Background Color; Send Personalized Emails to Multiple Recipients in Bulk.
- Super Filter: Create advanced filter schemes and apply to any sheets; Sort by week, day, frequency and more; Filter by bold, formulas, comment...
- More than 300 powerful features; Works with Office 2007-2019 and 365; Supports all languages; Easy deploying in your enterprise or organization.
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.