Excel Macro to Get Row Count: Choose the Right VBA Method

VBA & macros · Updated November 13, 2026

To count data rows in an Excel macro, count a column that has one nonblank entry for every record; for example, use VBA’s CountA on the data portion of column A. If you need the worksheet’s last used row instead, use Find to locate the last cell containing content—those are different measurements, so choose the method that matches what “row count” means in your workbook.

What “row count” can mean

Excel does not have one universal row-count value. A worksheet has a fixed grid of rows, but a macro usually needs to count records, populated cells, or the position of the last used row. A result is only useful when its meaning is clear.

  • Number of records: Count entries in a reliable key column, such as an order number or customer ID. This is usually the right answer for a data list.
  • Last used row number: Find the row number of the lowest cell containing content. This is useful when adding new data below existing content.
  • Rows in a range: Count the range’s height, whether or not every row contains data.
  • Visible rows: Count records currently visible after filtering or hiding rows. This needs a visibility-aware method.

A list can have 20 records and end on row 25 because it has a header and blank lines. In that case, “20 records” and “last used row 25” are both correct. Before writing a macro, decide whether blank lines, headers, totals, and hidden records should count.

Count records in a key column

If each data row has a value in column A, count its nonempty cells from the first data row down to the last nonempty cell. This example assumes the worksheet is named Data, row 1 is a header, and column A contains a value for every record. It handles an empty key column without reporting the header as a record.

Sub CountRecordsInColumnA()
    Dim ws As Worksheet
    Dim lastRow As Long
    Dim recordCount As Long

    ' Use the worksheet that contains the records.
    Set ws = ThisWorkbook.Worksheets("Data")

    ' Find the last nonempty cell in the key column.
    If Application.WorksheetFunction.CountA(ws.Columns("A")) = 0 Then
        recordCount = 0
    Else
        lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
        recordCount = Application.WorksheetFunction.CountA(ws.Range("A2:A" & lastRow))
    End If

    MsgBox "Records in column A: " & recordCount
End Sub

For example, if A1 contains the header and A2:A6 contains four IDs with one blank cell, the macro reports 4. The last-row search finds the bottommost entry, while CountA counts the populated cells above it. This works when column A is a dependable record identifier; it will undercount if valid records can have a blank in that column.

One detail matters: CountA counts formulas even when they display an empty string. If a key column contains formulas that return "", those cells are not truly empty to CountA. For that kind of sheet, use a column with actual identifiers, or define and implement a different test for what qualifies as a record.

Find the worksheet’s last used row

When the task is to append content or report how far down the worksheet content extends, search all cells for the last cell containing a value or formula. This example reports a row number, not the number of records. It ignores formatting-only cells because it searches for content.

Sub ShowLastUsedRow()
    Dim ws As Worksheet
    Dim lastCell As Range
    Dim lastRow As Long

    ' Search the active sheet; change this to a named worksheet if needed.
    Set ws = ActiveSheet

    ' Search by row, starting from the bottommost matching cell.
    Set lastCell = ws.Cells.Find(What:="*", After:=ws.Cells(1, 1), _
        LookIn:=xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows, _
        SearchDirection:=xlPrevious, MatchCase:=False)

    If lastCell Is Nothing Then
        lastRow = 0
    Else
        lastRow = lastCell.Row
    End If

    MsgBox "Last used row: " & lastRow
End Sub

If the last content is in row 250, the macro returns 250 even if only 100 rows contain records. A formula that displays a blank string still counts as content here because the search looks in formulas. The explicit search settings also make the search behavior more predictable than relying on options left over from Excel’s Find dialog.

For appending a record, a last-row search across the whole sheet may be inappropriate if notes or a separate report sit below the data. Search the key column instead, or use an Excel table. The target of the search should reflect the actual boundary of the data you are extending.

Count rows in an Excel table

If the data is formatted as an Excel table, its built-in row collection is often the clearest choice. A table adjusts as records are added, and ListRows.Count returns the number of data rows. It excludes the header and does not count a totals row as a data record.

Sub CountTableRows()
    Dim ws As Worksheet
    Dim dataTable As ListObject
    Dim recordCount As Long

    ' The worksheet and table names must match the workbook.
    Set ws = ThisWorkbook.Worksheets("Data")
    Set dataTable = ws.ListObjects("SalesTable")

    ' Count data rows, excluding the header and totals row.
    recordCount = dataTable.ListRows.Count
    MsgBox "Rows in SalesTable: " & recordCount
End Sub

Replace Data and SalesTable with the actual worksheet and table names. To create a table in Excel for Microsoft 365 on Windows, select a cell in the data and choose Insert > Table, then confirm whether the range has headers. Table names appear on the Table Design tab when the table is selected. If the worksheet or table name is wrong, VBA raises an error, so verify both names before running the code.

Tables are a good fit for recurring lists because new rows become part of the table. If your data is just a loose range, or includes several separate blocks, a table’s count may not describe the entire worksheet.

Choose a method for blanks, headers, and filters

For a normal record list, count a column that is filled for every record and begin below the header. For a table, count its data rows. For a task that needs the lowest occupied row anywhere on the sheet, use Find. Avoid treating the last row number as a record count unless the data starts at row 1 and has no gaps.

Blank rows inside a list create a common mismatch. Suppose records occupy rows 2 through 12, but row 7 is blank. The span from row 2 to row 12 contains 11 worksheet rows, while a count of IDs in column A returns 10 records. If you need to process every row in the span, the blank line may be relevant; if you need the number of records, it usually is not.

Filters introduce another distinction. CountA counts matching nonempty cells whether their rows are currently visible or filtered out. It does not mean “count what I can see.” For a filtered list, a worksheet subtotal designed to ignore filtered-out rows, or a macro that explicitly tests row visibility, is more appropriate. Also decide whether manually hidden rows should be included: that is a separate choice from whether a filter hides them.

Run and adapt the macro safely

In Excel for Microsoft 365 on Windows, open the VBA editor with Alt+F11. In the editor, choose Insert > Module, paste the code into the module, and edit the worksheet or table names as needed. Return to Excel and run the macro from the editor with the cursor inside it, or use Developer > Macros, select its name, and choose Run.

If the Developer tab is missing on Windows, enable it through File > Options > Customize Ribbon and check Developer. Excel for Mac also supports VBA, though keyboard shortcuts can depend on the keyboard and function-key settings; use Tools > Macro > Visual Basic Editor or the Developer tab if available. Excel for the web can open macro-enabled workbooks but does not run VBA macros; use desktop Excel to execute this code.

Save a backup copy before running a macro. VBA changes cannot be reversed with Ctrl+Z on Windows or Command+Z on Mac, even when a macro appears simple. If the macro only displays a count, the risk is limited, but adapting it to delete, move, or overwrite rows can change workbook data immediately. Save the file as an Excel Macro-Enabled Workbook (.xlsm) if you need to retain VBA code.

Formula alternative and common mistakes

If you only need a count displayed in a worksheet cell, a formula may be simpler than a macro. With record IDs in A2:A5 and entries in A2, A3, and A5, this formula returns 3:

=COUNTA(A2:A5)

That result is a count of nonempty cells, not a count of every row between the start and end of the range. A formula is useful when the count should update as cells change; a macro is useful when you need to use the count in a sequence of VBA actions or show it in a message. Neither approach knows what a valid record means unless the workbook’s layout makes that clear.

  • Counting the whole worksheet: A worksheet contains far more rows than a data list. Do not report the worksheet’s grid size as the number of records.
  • Using the wrong column: A column with optional notes or missing values may undercount records. Choose a required identifier field.
  • Including a header: Starting a count at row 1 includes the heading if it is nonempty. Begin at the first data row or use a table’s data-row count.
  • Confusing formatting with content: Formatting in distant cells can make Excel’s interface treat the used area as larger than the actual data. The Find example searches for content rather than formatting alone.

When the displayed answer seems wrong, inspect the exact range being counted, check for formulas returning empty strings, and confirm whether hidden or filtered rows should be included. Those checks usually reveal whether the issue is the VBA statement or a mismatch between the chosen method and the intended definition.

Frequently Asked Questions

How do I count rows in Excel VBA without counting the header?
Start the count at the first data row, such as A2 when row 1 is the header, and count a column that is populated for every record. For a formatted table, ListRows.Count excludes the header automatically.

Does End(xlUp) count every nonblank row?
No. End(xlUp) is commonly used to locate the last nonempty cell in a column. It returns a row position; it does not itself count populated cells or distinguish records from blank gaps.

How can I count rows when there are blank lines in the data?
Count nonempty cells in a reliable key column to get the number of records. If you instead need the number of worksheet rows from the first record through the last, calculate that span separately and remember it includes blank lines.

Will CountA ignore rows hidden by a filter?
No. CountA counts nonempty cells in its range even when their rows are hidden by a filter. Use a visibility-aware approach if the required result is only the visible records.

Can I run a VBA row-count macro in Excel for the web?
No. Excel for the web does not run VBA macros. Open the macro-enabled workbook in the Excel desktop app for Windows or Mac to run the VBA code.

Related

Try it: the Column Letter to Number: aA is 27, XFD is 16,384. Converts both ways.

More in VBA & macros

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.