VBA & Macro Problems

Find the First and Last YES and NO Rows in Excel with VBA

Steven ParkerSteven Parker Nov 14, 2026 869 views

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.
Before you start

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.

Solution 1Recommended

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.

1
Open the VBA editor

In Excel, open the Developer tab and select Visual Basic. If the Developer tab is hidden, enable it through Excel's ribbon customization settings.

2
Insert a module

In the VBA editor, select the workbook in the project pane, then choose Insert > Module.

3
Paste the macro

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

4
Run the macro

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.

5
Save the workbook

To keep the macro, choose File > Save As and select Excel Macro-Enabled Workbook (*.xlsm).

Use VBA Find to return the first and last matching rows
Adjust the search or output: The macro searches the active sheet's entire column B and writes fixed values to columns D and E. Change the column references in the code if your data or desired output is elsewhere, and rerun the macro after the source data changes.
Free Microsoft Office alternative

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.

Open and edit Microsoft Excel XLSX and XLS filesReview and organize YES and NO data in WPS SpreadsheetsUse a familiar spreadsheet interface for an easier transitionGet free spreadsheet tools in a lightweight office suite
microsoft office alternative - wps office

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.