Find the First and Last YES and NO Rows in Excel with VBA
Question details
Find the first and last row numbers containing YES and NO in column B using VBA.
- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- Column B contains consecutive YES and NO values, and either value may appear first.
- Observed behavior
- The user wants row numbers for the first and last occurrence of each value, regardless of which value appears first.
Open the workbook in a desktop version of Excel that supports VBA, and select the worksheet containing the values in column B. The macro below overwrites cells D1:E4, so make sure those cells are available.
Use VBA Find to return the first and last matching rows
Run a macro that searches column B in both directions and writes the results to D1:E4.
The macro matches whole-cell YES and NO values without regard to capitalization. It writes Not found if a value is absent and works regardless of which value appears first.
In Excel, open the Developer tab and select Visual Basic. If the Developer tab is hidden, enable it through Excel's ribbon customization settings.
In the VBA editor, select the workbook in the project pane, then choose Insert > Module.
Paste this code into the module: Sub FindYesNoRows() Dim ws As Worksheet Dim searchRange As Range Dim found As Range Dim terms As Variant Dim i As Long Dim outputRow As Long Set ws = ActiveSheet Set searchRange = ws.Columns("B") terms = Array("YES", "NO") For i = 0 To 1 outputRow = i * 2 + 1 ws.Cells(outputRow, "D").Value = "First " & terms(i) ws.Cells(outputRow + 1, "D").Value = "Last " & terms(i) Set found = searchRange.Find(What:=terms(i), _ After:=searchRange.Cells(searchRange.Rows.Count, 1), _ LookIn:=xlValues, LookAt:=xlWhole, _ SearchOrder:=xlByRows, SearchDirection:=xlNext, _ MatchCase:=False, SearchFormat:=False) If found Is Nothing Then ws.Cells(outputRow, "E").Value = "Not found" Else ws.Cells(outputRow, "E").Value = found.Row End If Set found = searchRange.Find(What:=terms(i), _ After:=searchRange.Cells(1, 1), _ LookIn:=xlValues, LookAt:=xlWhole, _ SearchOrder:=xlByRows, SearchDirection:=xlPrevious, _ MatchCase:=False, SearchFormat:=False) If found Is Nothing Then ws.Cells(outputRow + 1, "E").Value = "Not found" Else ws.Cells(outputRow + 1, "E").Value = found.Row End If Next i End Sub
Return to the worksheet you want to search, open Developer > Macros, select FindYesNoRows, and click Run. Read the labels in D1:D4 and the row numbers in E1:E4.
To keep the macro, choose File > Save As and select Excel Macro-Enabled Workbook (*.xlsm).

Work with Excel spreadsheets in WPS Office
WPS Office is a free, lightweight alternative for opening and editing spreadsheets in a familiar interface. Its Excel format compatibility makes it easy to bring existing files over, though it does not fix Excel's VBA environment and macro support can vary by version.

Frequently Asked Questions
What happens if column B contains no YES or no NO values?
The macro writes Not found in column E for both the first and last occurrence of the missing value.
Does the macro require YES to appear before NO?
No. It searches separately for YES and NO, so either value can appear first.
Will the macro find lowercase yes and no?
Yes. The Find calls use MatchCase:=False, so capitalization does not matter. LookAt:=xlWhole requires the entire cell value to match.
Do the results update when I change column B?
No. The macro writes row numbers as fixed values. Run FindYesNoRows again from Developer > Macros after changing the data.
Where should I save the macro?
Choose File > Save As and save the workbook as an Excel Macro-Enabled Workbook (*.xlsm) to retain the VBA code.




