ExcelInsider Team
If you regularly work with lists or tables in Excel, you might often need to count how many rows contain a specific value. Instead of applying filters or using formulas each time, VBA offers a quick and efficient way to automate this task.
In this article, we’ll show you different methods to count rows with specific data using VBA. You’ll learn how to use CountIf, loop through ranges, apply filters in code, and even create a custom function. We’ll also use a sample dataset where you can enter the search value in a cell, and the macro will return how many rows match that value.
Steps to count how many rows contain a specific keyword across multiple columns using VBA:
➤ Type the keyword to search for in cell E2 on Sheet1 (e.g., a class name like “9C”).
➤ Press Alt + F11 , insert a new module, and paste the following code:
Sub CountRowsWithKeyword()
Dim ws As Worksheet
Dim keyword As String
Dim lastRow As Long, lastCol As Long
Dim i As Long, j As Long
Dim matchCount As Long
Dim rowMatched As Boolean
Set ws = ThisWorkbook.Sheets("Sheet1")
keyword = Trim(ws.Range("E2").Value)
If keyword = "" Then
MsgBox "Please enter a keyword in cell E2.", vbExclamation
Exit Sub
End If
lastRow = ws.Cells(ws.Rows.Count, 1).End(xlUp).Row
lastCol = 4 ' Columns A to D
matchCount = 0
For i = 2 To lastRow
rowMatched = False
For j = 1 To lastCol
If Trim(ws.Cells(i, j).Value) = keyword Then
rowMatched = True
Exit For
End If
Next j
If rowMatched Then matchCount = matchCount + 1
Next i
MsgBox "Number of rows containing '" & keyword & "': " & matchCount, vbInformation
End Sub
➤ The macro scans columns A to D, row by row, starting from row 2.
➤ It compares each cell’s content to the keyword from E2.
➤ If a match is found anywhere in a row, it’s counted only once.
➤ Run it via Alt + F8 > CountRowsWithKeyword > Run.
Use CountA with a Specific Data to Count Matching Rows in Excel VBA
If you want to count how many rows contain a specific keyword in a key column (like IDs or names), VBA’s CountA combined with the CountIf function can help. You can enter the keyword in a cell, and the macro will count how many rows match that keyword.
For this example, our dataset is on Sheet1, with the ID values in column A. Cell E2 is where you enter the keyword (ID) to match. The macro will count how many rows contain that keyword and show the result.
Steps:
➤ Type the keyword in cell E2 (e.g., a class like “9C”).
➤ Press Alt + F11 , choose Insert >> Module, and paste:
Sub CountRowsWithKeyword()
Dim ws As Worksheet
Dim keyword As String
Dim lastRow As Long, lastCol As Long
Dim i As Long, j As Long
Dim matchCount As Long
Dim rowMatched As Boolean
Set ws = ThisWorkbook.Sheets("Sheet1")
keyword = Trim(ws.Range("E2").Value)
If keyword = "" Then
MsgBox "Please enter a keyword in cell E2.", vbExclamation
Exit Sub
End If
' Data ends in column D, so set lastCol = 4
lastRow = ws.Cells(ws.Rows.Count, 1).End(xlUp).Row
lastCol = 4 ' columns A–D only
matchCount = 0
' Loop through each data row
For i = 2 To lastRow
rowMatched = False
For j = 1 To lastCol ' scan columns A–D
If Trim(ws.Cells(i, j).Value) = keyword Then
rowMatched = True
Exit For ' stop scanning this row
End If
Next j
If rowMatched Then
matchCount = matchCount + 1
End If
Next i
MsgBox "Number of rows containing '" & keyword & "': " & matchCount, vbInformation
End Sub
➧ keyword = Trim(ws.Range("E2").Value) reads the user’s keyword from E2 and removes any extra spaces.
➧ lastRow and lastCol find the bottom‑most row and right‑most column with data, making the scan fully dynamic.
➧ The outer For i = 2 To lastRow loop processes each data row, skipping the header row.
➧ rowMatched resets to False for each row.
➧ The inner For j = 1 To lastCol loop checks every cell in that row if Trim(ws.Cells(i, j).Value) = keyword, the keyword is found in that row. rowMatched is set to True, and Exit For stops scanning the rest of that row to save time.
➧ After the inner loop, if rowMatched is True, matchCount increments by 1, ensuring each row is counted only once, even if the keyword appears in multiple columns.
➧ Finally, a MsgBox reports “Number of rows containing <keyword>: <matchCount>”, giving the total rows where the keyword appears at least once in any column.
➤ Return to Excel, press Alt + F8 , select CountRowsWithKeyword, and click Run.
➤ The count will be displayed in a message box.
Count Rows Containing All Non‑Blank Cells by Looping Through Each Row with CountA
Sometimes your data spreads across several columns, and you need to know which rows are completely filled, where every cell has a value. VBA can loop through each row and use CountA to verify that no blanks exist in that row before counting it.
For this example, assume your dataset is on Sheet1 in the range A2:D11. The macro will scan each row in this range and count how many rows have data in every cell.
Steps:
➤ Press Alt + F11 , choose Insert >> Module, and paste the code below.
Sub CountFullyFilledRows()
Dim ws As Worksheet
Dim dataRange As Range
Dim rowRange As Range
Dim countRows As Long
Dim colCount As Long
Set ws = ThisWorkbook.Sheets("Sheet1") ' change sheet name if needed
Set dataRange = ws.Range("A2:D11") ' adjust range as necessary
colCount = dataRange.Columns.Count ' number of columns to check
countRows = 0
' Loop through each row in the data range
For Each rowRange In dataRange.Rows
' Count non‑blank cells in the row
If Application.WorksheetFunction.CountA(rowRange) = colCount Then
countRows = countRows + 1 ' row is fully filled
End If
Next rowRange
MsgBox "Number of rows fully filled (no blanks) in A2:D11: " & _
countRows, vbInformation
End Sub 
➧ colCount stores the number of columns (4), so the code works even if you later expand the range
➧ For each row in dataRange, CountA(rowRange) returns the number of non‑blank cells.
➧ A row is counted only when CountA equals colCount, meaning every cell in that row is filled.
➧ The macro tallies such rows and displays the total in a message box.
➤ Back in Excel, press Alt + F8 , select CountFullyFilledRows, and click Run.
➤ A pop‑up shows how many rows in A2:D11 have no blanks at all.
Counting Rows Matching a Criteria Within the Used Range
Sometimes you need to count how many rows fall within a numeric range rather than match a keyword. By entering a minimum score in cell E2 and a maximum score in cell F2, you can have VBA scan the Score column (column C) in the used range and report how many rows meet that criterion.
For this example, your dataset is on Sheet1, in range A2:D11. The macro will count all rows whose Score falls between the values you enter in E2 and F2.
Steps:
➤ Enter the minimum score in E2 (e.g., 70).
➤ Enter the maximum score in F2 (e.g., 90).
➤ Press Alt + F11 to open the VBA Editor, then choose Insert >> Module.
➤ Paste the following code:
Sub CountRowsWithinScoreRange_UsedRange()
Dim ws As Worksheet
Dim usedRng As Range
Dim minScore As Double, maxScore As Double
Dim rowCount As Long
Dim cell As Range
Set ws = ThisWorkbook.Sheets("Sheet1")
' Read min and max from E2 and F2
If IsNumeric(ws.Range("E2").Value) And IsNumeric(ws.Range("F2").Value) Then
minScore = CDbl(ws.Range("E2").Value)
maxScore = CDbl(ws.Range("F2").Value)
Else
MsgBox "Please enter numeric values in E2 (min) and F2 (max).", vbExclamation
Exit Sub
End If
If minScore > maxScore Then
MsgBox "Minimum score cannot be greater than maximum score.", vbExclamation
Exit Sub
End If
' Focus on Score column (column C) within the used range
Set usedRng = ws.UsedRange.Columns(3) ' Column C
rowCount = 0
For Each cell In usedRng.Cells
If IsNumeric(cell.Value) Then
If cell.Value >= minScore And cell.Value <= maxScore Then
rowCount = rowCount + 1
End If
End If
Next cell
MsgBox "Number of rows with Score between " & minScore & " and " & _
maxScore & ": " & rowCount, vbInformation
End Sub
➧ It validates that both entries are numeric and that the minimum is not greater than the maximum.
➧ ws.UsedRange.Columns(3) targets column C (Score) within the used range.
➧ The macro loops through each cell in that column; if the score is numeric and falls within the specified range, the row counter increments.
➧ A message box reports how many rows meet the score range criteria.
➤ Return to Excel, enter your desired score range in E2 and F2, press Alt + F8 , select CountRowsWithinScoreRange_UsedRange, and click Run.
➤ A pop‑up will show the count of rows whose Score falls within the given range.
Counting Rows Within a Specific Range (No Keyword Required)
If you want to count how many rows exist within a particular rectangular range on your worksheet (regardless of whether cells are blank or filled), this method helps you do that easily. You’ll define the range you want to count, and the macro will return how many rows are present in it.
Steps:
➤ In Sheet1, drag your mouse to select any rectangular range.
➤ Press Alt + F11 to open the VBA Editor, then choose Insert >> Module.
➤ Paste the following code:
Sub CountRowsInSelectedRange()
Dim rng As Range
Dim rowCount As Long
' Use the current selection on the worksheet
Set rng = Selection
' Make sure the user has selected a range
If rng Is Nothing Then
MsgBox "No range selected.", vbExclamation
Exit Sub
End If
rowCount = rng.Rows.Count
MsgBox "Number of rows in the selected range " & _
rng.Address(False, False) & ": " & rowCount, vbInformation
End Sub
➧ rng.Rows.Count counts how many rows exist in that selection, regardless of how many columns it spans or whether cells are blank or filled.
➧ The macro exits with a gentle warning if no range is selected, preventing errors.
➧ A message box reports the row count along with the exact address of the selected range.
➤ After setting your desired range in the code, press Alt + F8 , select CountRowsInSpecificRange, and click Run.
➤ The macro will display the row count for the range you selected.
Frequently Asked Questions
Can I make the keyword search case‑insensitive or allow partial matches?
Yes. Replace the Trim(cell.Value) = keyword test with InStr(1, cell.Value, keyword, vbTextCompare) > 0 to ignore case and match any part of the cell text.
Will these macros adjust if I add new rows or columns later?
The “UsedRange” and “Selection” methods automatically expand with new data. If you hard‑code ranges (e.g., A2:D11), simply update the range reference or convert your data into an Excel Table for automatic resizing.
How can I count only visible rows when filters are on?
Wrap the row loop with If cell.EntireRow.Hidden = False Then …. This checks each row’s Hidden property so only visible (filtered‑in) rows are included in the count.
Are these counting methods fast on large datasets?
CountIf and CountA use Excel’s native engine and are very fast. For loops over hundreds of thousands of rows, turn off screen updating (Application.ScreenUpdating = False) and calculation (Application.Calculation = xlCalculationManual) to speed things up, then restore them afterwards.
Wrapping Up
VBA offers a quick, flexible way to count rows based on specific conditions, whether you’re looking for keywords, numeric ranges, or fully filled rows. In this article, you explored multiple VBA methods customized for different scenarios, all designed to save time and reduce manual effort. By using input cells or selected ranges, you can easily adapt the code to your needs.
