To count cells whose value is Jess, run a VBA macro that checks a chosen column and counts case-insensitive exact matches. The example below checks column A beneath its header, ignores extra spaces at the beginning or end of a cell, and displays the total. You can change the name or column to suit your sheet. Use the desktop version of Excel; Excel for the web cannot run VBA macros.
What You’ll Need
- Excel for Microsoft 365 on Windows, with a workbook containing the names you want to check. The steps also work in desktop Excel for Mac, though the menu names and keyboard shortcuts can differ.
- A clear idea of where the names are stored. This example assumes a header in A1 and names in A2 downward.
- A macro-enabled workbook saved as an .xlsm file. Save a separate copy before running a macro because VBA changes cannot be undone with Ctrl+Z.
Step-by-Step
- Check how the names are recorded. Open the worksheet and confirm that the names are in one column, with a header in the first row. The main macro counts a cell when its complete text is Jess. It will count Jess, JESS, or a cell containing spaces around Jess, but it will not count Jess Smith or My friend Jess. If your data is in another column or begins on another row, you will adjust the range logic after adding the macro.
- Save a working copy as a macro-enabled workbook. Choose File > Save As, select a location, and set the file type to Excel Macro-Enabled Workbook (*.xlsm). This format preserves VBA code. If Excel shows a warning about macro-enabled files, confirm that you want to keep the format. Saving a copy first also gives you a clean version to return to if a macro is changed accidentally.
- Open the Visual Basic Editor. In Excel for Windows, press Alt+F11, or choose Developer > Visual Basic. If the Developer tab is missing, go to File > Options > Customize Ribbon, select the Developer check box, and choose OK. In Excel for Mac, use Tools > Macro > Visual Basic Editor; the shortcut may be Option+F11 or Fn+Option+F11 depending on your keyboard. The Visual Basic Editor is not available in Excel for the web.
- Insert a standard module. In the Visual Basic Editor, select your workbook in the Project pane. Choose Insert > Module. A blank code window should appear. A standard module is a suitable place for a reusable macro; do not paste this procedure into a worksheet cell or into the Immediate window.
- Paste the macro into the module. This version finds the last used row in column A, then checks each cell from A2 to that row. It avoids scanning every row in Excel and skips cells containing errors.
Option Explicit
Sub CountJess()
Dim ws As Worksheet
Dim lastRow As Long
Dim searchRange As Range
Dim cell As Range
Dim targetName As String
Dim matchCount As Long
' Use the active sheet; change this if needed.
Set ws = ActiveSheet
targetName = "Jess"
' Find the last used row in column A.
lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
If lastRow < 2 Then
MsgBox "No names were found below the header in column A.", vbInformation
Exit Sub
End If
Set searchRange = ws.Range("A2:A" & lastRow)
For Each cell In searchRange.Cells
If Not IsError(cell.Value2) Then
If StrComp(Trim$(CStr(cell.Value2)), targetName, vbTextCompare) = 0 Then
matchCount = matchCount + 1
End If
End If
Next cell
MsgBox targetName & " appears in " & matchCount & " cells on " & _
ws.Name & ".", vbInformation, "Count Jess"
End Sub
- Review the sheet and range assumptions. The macro uses whichever worksheet is active when you run it. It assumes row 1 is a header and the names are in column A. If your names are in column C, replace each occurrence of the column reference A in the last-row and range lines with C. If the list starts at row 5, change the range start from row 2 to row 5 and update the empty-list check so it compares the last row with 4. Keep the references consistent.
- Run the macro and read the result. Return to Excel, click the worksheet you want to check, and press Alt+F8 on Windows. Select CountJess and choose Run. You can also choose Developer > Macros, select the macro, and choose Run. On Mac, use Tools > Macro > Macros if that menu is available in your Excel version. A message box reports how many cells matched and names the active sheet. Check that the correct worksheet was active before you ran it.
- Verify the total with a quick manual check. Filter the name column for Jess, or use Find to inspect matches, and compare a small sample with the reported count. The macro counts matching cells, not the number of times the letters appear inside a cell. For example, one cell containing JessJess contributes one matching cell in this macro because that whole cell is not equal to Jess; the macro does not count partial text.
Common Mistakes
Expecting partial matches. The main procedure deliberately tests the complete cell value. A cell containing Jess Smith is not an exact Jess match. If the list includes full names and you want any cell containing Jess, use the occurrence-count option in Tips instead, or change the matching logic deliberately. Be aware that a partial match could also count Jessica.
Running it on the wrong sheet. Because the code sets ws to ActiveSheet, it checks the sheet selected at run time. Click the correct sheet tab first. For a fixed worksheet, you can replace Set ws = ActiveSheet with Set ws = ThisWorkbook.Worksheets("Roster"), changing Roster to the exact worksheet tab name.
Using the wrong start row or column. If the data begins in row 1, the example skips that row. If the data is in another column, the macro may return zero or a misleading count. Update both the last-row reference and the range definition, then check the assumptions against the actual sheet.
Saving in the wrong format or blocking macros. A workbook saved as .xlsx cannot preserve VBA code. Also, Excel may disable macros in a downloaded or untrusted file. Only enable macros in files you trust, and follow your organization’s security guidance rather than lowering macro security settings broadly.
Tips
To count a different exact name, change targetName = "Jess" to the name you want. Keep the quotation marks. The comparison uses vbTextCompare, so capitalization does not matter; changing it to vbBinaryCompare would make the comparison case-sensitive. Trim$ removes ordinary spaces at the ends of a value, but it does not remove every possible invisible character, such as a nonbreaking space copied from a web page.
If you want a count of cells containing Jess anywhere within their text, replace the exact-match test inside the loop with a case-insensitive InStr test. That counts each matching cell once, whether Jess appears once or several times in that cell. It can also match Jessica, so use it only if that is appropriate for your data:
If InStr(1, CStr(cell.Value2), targetName, vbTextCompare) > 0 Then
matchCount = matchCount + 1
End If
If you need the number of appearances rather than the number of cells, repeat the search within each cell and add one for every occurrence. Advance the search position by Len(targetName) after each match to avoid counting the same letters repeatedly. Decide whether you want to include longer words such as Jessica before choosing this method. For a list with thousands of rows, the loop is generally easier to adapt and review than a manual count, while a bounded range keeps it from checking unused worksheet rows.
You can also write the result into a worksheet cell rather than displaying a message. For example, add a line such as ws.Range("B1").Value = matchCount after the loop, and make sure B1 is not data you need to preserve. A macro that writes to a cell changes the workbook, so keep the backup and remember that Ctrl+Z will not reverse the change.
Troubleshooting
The macro does not appear in the Macros list: Confirm that the code is in a standard module in the correct workbook, that it begins with Sub CountJess(), and that the file is saved as .xlsm. If you pasted the code into a worksheet’s code window, create a module with Insert > Module and move the procedure there.
Excel says macros are disabled: Close the workbook and check that it came from a source you trust. If appropriate, use Excel’s security notification to enable content for that file. Do not enable all macros globally just to run one procedure. On managed work devices, ask your administrator if policy prevents VBA.
The total is zero but Jess is visible: Check whether Jess is part of a longer value, such as Jess Smith, or whether the data is in a different column or sheet. Inspect the cell’s formula bar for hidden characters. A formula that displays Jess is included because the macro reads its calculated value, but a formula error is skipped.
The result is too high: The macro counts each matching cell once, including duplicate names in different rows. It does not count unique people. If you meant unique Jess records, define what makes a record unique, such as an ID in another column, before changing the code. Also check that the active sheet has not got unrelated Jess values in column A.
The editor highlights a line as a syntax error: Make sure the VBA code was copied without HTML entities. In the displayed code block, ampersands may appear as & because of HTML formatting; in the Visual Basic Editor, use the actual ampersand character &, not the text &. Similarly, paste ordinary quotation marks. Then choose Debug > Compile VBAProject in the editor if that command is available, and correct any highlighted line.
Frequently Asked Questions
Does this count Jess and JESS as the same name?
Yes. The macro uses vbTextCompare, which compares text without distinguishing uppercase and lowercase. It also trims spaces at the beginning and end, so a cell containing a space before Jess still counts.
Will it count Jess in a full name such as Jess Brown?
No. The main macro counts only cells whose full value is Jess after trimming outside spaces. Use a partial-match test if you want Jess Brown included, and consider that it may also match names such as Jessica.
Can I count names in more than one column?
Yes. Set the search range to a rectangular block, such as A2:C1000, if every cell in that block should be checked. The macro will count matching cells across all included columns. Avoid including headers or unrelated data unless you want those cells counted too.
Can I run the macro in Excel for the web?
No. Excel for the web can open and edit many .xlsm workbooks, but it cannot create or run VBA macros. Open the file in the desktop Excel app to use this procedure.
Can I undo the macro with Ctrl+Z?
No. Excel clears its normal undo history when VBA runs. This counting version only displays a message and does not change worksheet values, but save a backup before modifying or extending it to write results into the sheet.
Related
- How to Copy Multiple Sheets in Excel Without Errors
- How to Copy Only Filtered Data in Excel Without Hidden Rows
- Copy Link in Excel: Steps for Cells, Files, and Sheets
Try it: the Column Letter to Number: aA is 27, XFD is 16,384. Converts both ways.
More in VBA & macros
- Excel VBA Get Array Dimensions: Bounds, Rank, and SizeIn Excel VBA, use LBound(array, dimension) and UBound(array, dimension) to get an array’s lower and upper bounds; its…
- How to Put a Photo in a VBA UserFormTo put a photo in an Excel VBA UserForm, add an Image control, then either set its Picture…
- VBA PivotTable Range: Source, Output, and ExamplesIn VBA, a PivotTable’s source range is the worksheet data used to build or refresh it, while its…
- VBA GoTo: Syntax, Examples, and Safer AlternativesIn VBA, GoTo is a statement, not a function: it transfers execution to a labeled line in the…
- Excel VBA: Find the First Non-Empty Row in a ColumnTo find the first non-empty row in an Excel column with VBA, use the range’s Find method with…
- Excel VBA Copy and Paste Formulas Between Cells SafelyTo copy a formula to another cell with Excel VBA, use Copy followed by PasteSpecial Paste:=xlPasteFormulas. This pastes…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.