excelavon

Excel VBA Find the First Empty Row

VBA & macros · Updated November 3, 2026

Excel VBA Find the First Empty Row

To append data after the last entry in a known column, use Cells(Rows.Count, "A").End(xlUp).Row + 1, with a check for an entirely blank column. If you mean the first blank row inside a list, scan the rows across the columns that define a complete record instead: “next row after the data” and “first empty row” are not always the same thing.

Choose what “first empty row” means

Excel VBA can identify several different kinds of empty row. The right method depends on how the worksheet is organized and what the macro should do. For a simple list with no gaps, most people want the row immediately below the last entry in a key column. If the list has gaps, they may instead want the earliest row with no record data. If entries can be spread across multiple columns, an empty cell in one column does not necessarily mean the whole record row is empty.

Before choosing code, decide which columns make a row occupied. For example, if each record has an ID in A and details in B:D, you might treat a row as occupied when any cell in A:D contains something, or only when the ID in A is present. That decision affects whether a partially filled row is available for reuse.

  • Append after a key column: useful for a continuous list where column A is filled for every record.
  • Find the first blank record row: useful when gaps should be filled and a record is defined by several columns.
  • Append after any content on the sheet: useful for a general worksheet, but it may place new data below notes or unrelated content.

Append after the last entry in a known column

The familiar End(xlUp) pattern starts at the bottom of a worksheet column and moves upward to the last non-empty cell. Adding one gives the next row. This approach is fast and simple when the chosen column is reliably populated for every record. Always qualify both the worksheet and the row limit; otherwise, VBA may use whichever sheet happens to be active.

This macro uses column A as the key column. If the sheet is completely empty, it selects row 1. If A1 contains a header, the next available row is 2. It writes a sample value so you can see where the result goes; replace that assignment with the work your macro needs to do.

Sub AppendAfterLastEntry()
    Dim ws As Worksheet
    Dim nextRow As Long

    ' Use a specific sheet in the workbook containing this macro.
    Set ws = ThisWorkbook.Worksheets("Data")

    If Application.CountA(ws.Columns("A")) = 0 Then
        nextRow = 1
    Else
        nextRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row + 1
    End If

    ' Example: write a new item in the key column.
    ws.Cells(nextRow, "A").Value = "New item"
End Sub

Change "Data" to the actual worksheet tab name and change "A" if another column is your dependable key. ThisWorkbook means the workbook that contains the VBA code. If the macro is stored in a personal macro workbook but should act on the currently selected file, you may need ActiveWorkbook instead; be aware that the active workbook can change while a macro runs.

What the End(xlUp) method does—and does not do

ws.Cells(ws.Rows.Count, "A").End(xlUp).Row is analogous to starting at the bottom of column A and pressing Ctrl+Up. It returns the row number of the next cell reached by moving upward through the column. Adding one gives the row after that result. It does not search for the earliest gap between records. If A2 and A4 contain values while A3 is blank, the expression returns row 5, not row 3.

The CountA check handles a common edge case: when column A has no entries, the upward navigation still lands at row 1, so blindly adding one would choose row 2. With a header in A1, however, the column is not empty and row 2 is correctly chosen. CountA counts formulas even when they display an empty string, so a formula in the key column can make that row count as occupied. Formatting alone does not count as content.

A key column is only a good anchor if it is filled consistently. If records sometimes have a blank ID but contain information in B or C, this method may overwrite or skip rows depending on the layout. In that case, define record occupancy across the relevant columns or use a structured Excel table.

Find the first blank record row within a range

To fill the earliest gap, test each row from the first data row down. The example below considers a row empty only when all four cells in A:D are empty, and searches rows 2 through 10,000. Adjust the first row, columns, and limit to match your sheet. A finite limit makes the intended search area clear and avoids scanning every worksheet row unnecessarily.

Sub FindFirstBlankRecordRow()
    Dim ws As Worksheet
    Dim r As Long
    Dim firstBlank As Long

    Set ws = ThisWorkbook.Worksheets("Data")
    firstBlank = 0

    ' Treat a row as empty when A:D contain no values or formulas.
    For r = 2 To 10000
        If Application.CountA(ws.Range("A" & r & ":D" & r)) = 0 Then
            firstBlank = r
            Exit For
        End If
    Next r

    If firstBlank = 0 Then
        MsgBox "No blank record row was found in the search range."
    Else
        MsgBox "First blank record row: " & firstBlank
    End If
End Sub

In the HTML display, the ampersands in VBA string concatenation are written as & so they remain valid HTML. When entering the macro in the Visual Basic Editor, use the normal VBA ampersand character: for example, "A" & r in this displayed block corresponds to "A" & r in the code editor. The check uses CountA, so a formula returning "" is treated as content. If that is not the intended rule, use a test designed around the actual definition of an empty record.

This loop is a straightforward option for modest, bounded ranges. A million-row scan can be slow, especially if each iteration reads worksheet cells. Narrow the search to the true data columns and a realistic row limit, or use a table when the data is naturally tabular.

Find the row after content anywhere on the sheet

If column A is not a reliable anchor, a Find search can locate the last row containing a formula or value anywhere on the sheet. The function below searches by rows in reverse order. It returns row 1 for a sheet with no found content; otherwise, it returns the row after the last found cell. This is an append position, not a search for a gap in the middle.

Function NextRowAfterSheetContent(ByVal ws As Worksheet) As Long
    Dim lastCell As Range

    ' Search formulas as well as typed values; set Find options explicitly.
    Set lastCell = ws.Cells.Find( _
        What:="*", _
        After:=ws.Cells(1, 1), _
        LookIn:=xlFormulas, _
        LookAt:=xlPart, _
        SearchOrder:=xlByRows, _
        SearchDirection:=xlPrevious, _
        MatchCase:=False, _
        SearchFormat:=False)

    If lastCell Is Nothing Then
        NextRowAfterSheetContent = 1
    Else
        NextRowAfterSheetContent = lastCell.Row + 1
    End If
End Function

Excel remembers some Find dialog settings between searches. Supplying the important arguments makes the macro less dependent on a user’s previous Find operation. Searching with LookIn:=xlFormulas means a formula is considered present even if its displayed result looks blank. Formatting by itself is not a value or formula, so it is not the same as used content for this purpose. If unrelated notes, titles, or formulas exist below the data, this method will append after them; a dedicated data column or table is often safer.

Use an Excel table for growing records

When a range is a proper Excel table, VBA can add a table row directly instead of calculating a worksheet row number. Tables expand as records are added and can carry formulas and formatting into new rows. In the VBA editor, a table is represented by a ListObject, and its ListRows.Add method adds a data row. This is a good fit when your real goal is “add a record to this table,” rather than “find an empty worksheet row.”

For example, a table named SalesTable on the Data worksheet can be referenced through ws.ListObjects("SalesTable"). The table name is set or viewed from the Table Design tab when the table is selected. A table may have a totals row, filters, or other layout details, so adding through the table object is generally less error-prone than writing just below its visible range. If the table contains no data rows yet, adding a row still creates a data row within the table.

Common mistakes and edge cases

  • Using the active sheet by accident: Cells and Rows.Count without a worksheet qualifier can refer to the active sheet. Use ws.Cells and ws.Rows.Count consistently.
  • Confusing an empty cell with an empty-looking cell: a formula such as one returning an empty string still exists in the cell. CountA counts that formula; visual appearance is not a reliable test of emptiness.
  • Choosing a column with missing keys: End(xlUp) only follows the column you specify. If some records lack an entry there, the result may not reflect the last occupied record.
  • Expecting it to fill gaps: adding one to the last occupied row appends at the bottom. It does not reuse blank rows above the last record.
  • Reaching the worksheet limit: an Excel worksheet has 1,048,576 rows. If the last occupied row is already the final row, adding one produces an invalid row number. Check that condition before writing when a sheet may be full.

Hidden rows do not change the basic meaning of these methods: a hidden row can still contain data and can be returned as the last occupied row. Also, cells with formatting but no values or formulas may make a worksheet appear used, but they are not necessarily records. Keep the search area aligned with the data definition rather than relying on what looks blank on screen.

Enter and run the macro safely

In Excel for Microsoft 365 on Windows, open the Visual Basic Editor with Alt+F11, choose Insert > Module, and paste the code into the module. On Mac, Option+F11 opens the editor on many keyboards; you may also need the Fn key, depending on keyboard settings. You can use the Developer tab’s Visual Basic button on either platform. Save a macro-enabled workbook as .xlsm if you want to keep the VBA code.

Before running a macro that changes cells, save a copy of the workbook. VBA changes cannot be undone with Ctrl+Z, and Excel for Mac likewise does not provide normal Undo for VBA edits. The sample append macro writes the text “New item”; remove or replace that line before running it on real data. Test the row-selection logic on a copy and confirm the chosen worksheet, key column, and header row first.

Frequently Asked Questions

How do I find the next empty row in column A?
For a continuous list, check whether column A is empty with CountA; otherwise use Cells(Rows.Count, "A").End(xlUp).Row + 1. Qualify the expression with the worksheet variable, and account for whether a blank sheet should start at row 1 or row 2 beneath a header.

Does End(xlUp) find the first empty row?
No. It finds the last non-empty cell when starting from the bottom of a column. Adding one returns the row after that cell. To find an earlier gap, examine rows from the top and test the relevant record columns.

Why does the result become row 2 when the column is empty?
Moving upward from the bottom of an entirely blank column reaches row 1, so adding one gives row 2. Use a blank-column check such as Application.CountA(ws.Columns("A")) = 0 to return row 1 instead, or deliberately return row 2 if row 1 is reserved for a heading.

Do formulas that display blank count as empty?
Usually not for these methods. CountA counts a cell containing a formula even if the formula displays an empty string, and a search using LookIn:=xlFormulas can find the formula itself. Decide whether “empty” means no formula or no visible result, then choose a test that matches.

Can I find the first empty row across the whole worksheet?
There is no single universal answer until you define which columns make a row occupied. A row with nothing in A but a note in D may be empty according to a key-column rule and occupied according to a whole-record rule. Scan the columns that define your records, or add records through an Excel table.

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.