Excel VBA: Find the First Non-Empty Row in a Column

To find the first non-empty row in an Excel column with VBA, use the range’s Find method with What:="*" and search from the bottom so the search wraps to the top. For example, search column A and check whether the result is Nothing before reading its row number.

VBA code for the first non-empty row

This macro searches column A on a worksheet named Data and displays the worksheet row of the first match. It treats a cell containing a formula as occupied, even if that formula displays an empty string; the distinction is explained below.

Sub FindFirstNonEmptyRow()
    Dim ws As Worksheet
    Dim searchRange As Range
    Dim foundCell As Range

    Set ws = ThisWorkbook.Worksheets("Data")
    Set searchRange = ws.Columns("A")

    ' Start after the last cell so the search wraps to the top.
    Set foundCell = searchRange.Find( _
        What:="*", _
        After:=searchRange.Cells(searchRange.Cells.Count), _
        LookIn:=xlFormulas, _
        LookAt:=xlPart, _
        SearchOrder:=xlByRows, _
        SearchDirection:=xlNext, _
        MatchCase:=False, _
        SearchFormat:=False)

    If foundCell Is Nothing Then
        MsgBox "Column A has no matching cells."
    Else
        MsgBox "First matching row: " & foundCell.Row
    End If
End Sub

Change Data to the exact worksheet tab name and change A to the column you want to inspect. Before running a macro, save a copy of the workbook: VBA actions generally cannot be reversed with Ctrl+Z. Save a macro-containing workbook as an Excel Macro-Enabled Workbook (.xlsm).

How Find locates the first match

The Find method searches a range for a value or text pattern. The asterisk in What:="*" is a wildcard that stands for a sequence of characters, and this is a widely used way to locate a cell containing something in a worksheet range. The method returns a Range object for a match. If there is no match, it returns Nothing, so the code must test for that before trying to read foundCell.Row.

Excel’s Find operation can wrap around from the end of its range to the beginning. Setting After to the last cell in column A and choosing SearchDirection:=xlNext makes the first cell considered after that starting point the top of the column. SearchOrder:=xlByRows makes the search proceed by rows. In a single column, row order is straightforward; the setting becomes especially important when searching a multi-column block.

The code supplies the main Find options instead of relying on whatever settings were used the last time someone opened Excel’s Find dialog. Excel can retain Find options between searches, so explicitly setting the match type, direction, and search order makes a macro’s behavior more predictable.

Choose the range and define what counts as non-empty

Searching an entire column is convenient when the data could grow. A worksheet in current Excel versions has 1,048,576 rows, so ws.Columns("A") covers A1:A1048576. If you know the data starts at row 2 and want to ignore a header, set the search range to A2 through the bottom of the worksheet instead. The returned Row is still the worksheet row number, not a position counted from the start of the range.

There is no single definition of non-empty that fits every workbook. Consider these cases before choosing the search method:

A constant value or text: Usually counts as occupied, including a number such as 0.
A formula: The sample uses LookIn:=xlFormulas, so a formula is considered present even when its displayed result looks blank.
A formula returning an empty string: It may look like a blank cell on the sheet, but the cell contains a formula. Use a value-based test or a loop if you want to treat that result as empty.
Spaces: A cell containing a space is not truly empty. It can be hard to see, but a non-empty test may find it.
Formatting alone: A fill color, border, or number format does not by itself make a cell contain content for this search.

With LookIn:=xlFormulas, the key question is whether the cell contains a formula or constant. If your definition is instead based on displayed values, use LookIn:=xlValues and check the results against your workbook, particularly when formulas return empty strings or cells have unusual display formats.

Ignore headers or search only part of a column

To begin below a heading, change the range rather than finding a result and then adding one to its row. For example, use ws.Range("A2:A" & ws.Rows.Count) as the search range. That excludes A1 but still allows the search to reach the last worksheet row. If your data is known to stop at row 500, a bounded range such as A2:A500 can make the intended scope clearer and avoids searching cells outside the data area.

A bounded range is also useful when the worksheet contains notes or unrelated content farther down the column. The first match means the first matching cell in the range you supplied, not necessarily the first cell that belongs to a particular table. If a header is in row 1, a note is in row 3, and the records begin in row 5, searching from row 2 will find the note first. Narrow the search to the actual data area or add a separate test for what qualifies as a record.

Find the first row with data in several columns

If a record uses columns A through D, searching only column A can miss a row where A is blank but B, C, or D contains data. Search the whole relevant rectangle instead. With a range that begins at A1 and ends at D500, SearchOrder:=xlByRows looks across rows in order, so the returned match belongs to the earliest row containing a match anywhere in that block.

Sub FindFirstRowInBlock()
    Dim ws As Worksheet
    Dim searchRange As Range
    Dim foundCell As Range

    Set ws = ThisWorkbook.Worksheets("Data")
    Set searchRange = ws.Range("A1:D500")

    ' Search the block in row order, starting at its end.
    Set foundCell = searchRange.Find( _
        What:="*", _
        After:=searchRange.Cells(searchRange.Cells.Count), _
        LookIn:=xlFormulas, _
        LookAt:=xlPart, _
        SearchOrder:=xlByRows, _
        SearchDirection:=xlNext, _
        MatchCase:=False, _
        SearchFormat:=False)

    If foundCell Is Nothing Then
        MsgBox "The block contains no matching cells."
    Else
        MsgBox "First matching row: " & foundCell.Row
    End If
End Sub

This finds the first row with any matching cell, not the first row where every required field is filled in. If a valid record must have both an ID in A and a date in D, a separate row-by-row condition is more appropriate. Likewise, expand the range if records may extend beyond row 500; otherwise, later records are deliberately outside the search.

Why End(xlUp) is not the same method

A common VBA expression such as Cells(Rows.Count, "A").End(xlUp).Row finds the last non-empty cell when moving upward from the bottom of a column. It answers the opposite question from this article’s main example. It is useful for locating the end of a list, but it does not directly identify the first occupied row.

One workaround is to start at the top and use End(xlDown), but that can stop at the first blank in a continuous block or behave unexpectedly when the starting cell is blank. It is therefore a poor general substitute when the column may contain gaps. Find is a better fit when the aim is to search for a match throughout a range and return the first row in top-to-bottom order.

Another distinction is that Excel’s UsedRange is not a reliable definition of the first row containing meaningful data. It can include cells that were previously edited or formatted, and it describes a used area rather than answering whether a chosen cell contains a value. Search the specific column or data block instead of assuming the used range represents the records.

Common problems and practical checks

The macro says the worksheet is missing: The worksheet name in Worksheets("Data") must match the tab name exactly. If the code is stored in a different workbook from the data, confirm that ThisWorkbook refers to the intended file.
The result is a heading: Start the range below the header, or explicitly exclude the heading row.
A visually blank row is returned: Look for formulas returning an empty string or cells containing spaces. Decide whether those should count as occupied.
A match seems to be skipped: Confirm that the search range includes the row and column, and that the chosen LookIn setting matches your definition of content.
The macro will not run: Excel for the web does not run VBA macros. Open the workbook in desktop Excel and use a macro-enabled file format. On Windows, Alt+F11 opens the Visual Basic Editor; on Mac, Option+F11 may require the Fn key depending on keyboard settings.

VBA is available in desktop Excel for Windows and Mac, although keyboard behavior and some interface details can differ. A macro cannot run in Excel for the web, even if the workbook contains VBA. If a workbook is shared with people who use the web version, provide a non-macro alternative or have them open it in desktop Excel.

Frequently Asked Questions

How do I find the first non-empty row in column A? Set the search range to ws.Columns("A"), use Find with What:="*", and check for Nothing. The first result’s Row property gives its worksheet row.

How do I skip the header row in VBA? Begin the search range at row 2, such as A2 through the last worksheet row. The result still reports its actual worksheet row number.

Does Find count a formula that displays a blank? With LookIn:=xlFormulas, the cell’s formula is the thing being searched, so it can count as occupied even if the displayed result is blank. If you mean non-empty by displayed value, use a value-oriented test and verify how formula results should be handled.

What does Find return if the column is empty? It returns Nothing. Test that condition before accessing properties such as Row; otherwise, VBA will raise an error.

Can I use this to find the first non-empty row across a table? Yes. Search the table’s full rectangular range and set SearchOrder:=xlByRows. The result is the earliest row with a match anywhere in that range, not necessarily a row with every field completed.

Can I run VBA in Excel for the web? No. Open the workbook in desktop Excel for Windows or Mac to run VBA. If the file contains macros, save it in a macro-enabled format such as .xlsm.

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.