excelavon

VBA Find the Next Empty Row in Excel Automatically

VBA & macros · Updated November 3, 2026

VBA Find the Next Empty Row in Excel Automatically

To find the next row for appending data in VBA, use Cells(Rows.Count, "A").End(xlUp).Row + 1, replacing column A with a column that is filled for every record. This returns the row after the last occupied cell in that column; if you mean the first blank row inside a list, use a different check.

The quick answer: append after the last row

For a worksheet with a header in row 1 and records below it, this is the usual approach:

Dim nextRow As Long
nextRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row + 1

Here, ws is a worksheet variable you have already set, such as Set ws = ThisWorkbook.Worksheets("Sheet1"). Column A is the key column: it should contain a value on every row that represents a record. If each record has an ID in column A, the expression finds the last ID and returns the following row. You can then write values into that row, for example with ws.Cells(nextRow, "A").Value = "New item".

The method works in desktop Excel for Microsoft 365 on Windows and Mac. VBA macros cannot run in Excel for the web. Before running a macro, save a copy of the workbook: macro actions cannot be undone with Ctrl+Z.

How End(xlUp) finds the row

ws.Rows.Count gives the number of rows on the worksheet. The expression starts at the bottom of the chosen column and uses End(xlUp) to move upward to the next nonempty cell, much like pressing Ctrl+Up in a column. Reading its Row property gives that cell’s row number; adding one produces the append row.

For example, if column A contains a header in A1 and IDs through A27, the expression returns 28. A blank in the middle does not stop it: if A14 is blank but A27 contains an ID, the result remains 28. That behavior is useful when appending, because a gap in the data should not cause a new record to overwrite a later one.

The header matters when the key column has no data. If A1 contains a header and the rest of the column is empty, the method returns row 2, which is generally what you want. If the entire column is empty, the upward search reaches row 1 and adding one returns row 2, leaving row 1 unused. Decide whether your first data row is row 1 or row 2, and handle an entirely empty sheet if that distinction matters.

A complete append macro

This example finds the next row based on IDs in column A, then writes a new ID and description into columns A and B. Change the worksheet name and values to match the workbook. The code assumes row 1 is a header.

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

    ' Use the worksheet that stores the records.
    Set ws = ThisWorkbook.Worksheets("Sheet1")

    ' Find the next row using the ID column.
    nextRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row + 1

    ' Write the new record into that row.
    ws.Cells(nextRow, "A").Value = 1005
    ws.Cells(nextRow, "B").Value = "Replacement part"
End Sub

To add the macro in Excel for Windows, open the Visual Basic Editor with Alt+F11, choose Insert > Module, and paste the code into the module. Run it with F5 while the cursor is inside the procedure, or return to Excel and run it from the Macro dialog. On Mac, open the Visual Basic Editor from the Developer tab or the Tools > Macro menu; keyboard shortcuts can vary with the keyboard and macOS settings. Save the workbook as an Excel Macro-Enabled Workbook (.xlsm) to retain the VBA code.

Choose the right column to check

The most important choice is not the letter A itself, but whether that column reliably marks each record. If you use a column with occasional blanks, the method may return a row that is already occupied elsewhere. For instance, if column A contains optional notes and column B contains a required customer ID, use column B as the reference.

  • Use an ID or other required field when every valid record has one.
  • Use a date or name column only if it is filled consistently for all records.
  • Do not use a calculated or optional field if blank cells in it are normal.
  • Qualify the worksheet reference so the macro does not depend on whichever sheet happens to be active.

A reference like Cells(Rows.Count, "A") without a worksheet qualifier uses the active sheet. In a larger macro, explicitly use ws.Cells and ws.Rows.Count together. That makes the target clear and reduces the chance of writing into the wrong worksheet.

Find the last used row anywhere on the sheet

Sometimes no single column is complete, but you want to append below the last cell containing content anywhere on the worksheet. The Find method can search across the sheet. Specify its options rather than relying on settings left by a previous Find operation in Excel.

Sub FindNextRowOnSheet()
    Dim ws As Worksheet
    Dim lastCell As Range
    Dim nextRow As Long

    Set ws = ThisWorkbook.Worksheets("Sheet1")

    ' Search formulas and values, from the bottom of the used content.
    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
        nextRow = 1
    Else
        nextRow = lastCell.Row + 1
    End If
End Sub

This version treats a formula as content even when that formula displays an empty string, because it searches formulas. It also checks the whole worksheet rather than one key column, so the next row is below the lowest row containing content in any column. That may be exactly right for a varied layout, but it can place new records far below a table if another section lower on the sheet contains notes or totals. If the worksheet is completely empty, it sets the next row to 1; change that to 2 if your macro always reserves row 1 for headers.

Appending is not the same as finding a gap

“Next empty row” can mean either the row after all existing records or the first blank row within a list. The End(xlUp) pattern answers the first question. It deliberately skips over internal gaps when finding the last occupied row. If a list has data in rows 2, 3, and 5, it returns row 6, not row 4.

To locate the first genuinely empty cell in a key column, scan the data area from its first data row. This example considers a cell empty only when it contains no value or formula; a formula that displays an empty string still counts as occupied.

Sub FindFirstBlankInList()
    Dim ws As Worksheet
    Dim r As Long
    Dim firstBlank As Long
    Dim lastRow As Long

    Set ws = ThisWorkbook.Worksheets("Sheet1")
    lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
    firstBlank = 0

    ' Row 2 is the first data row; row 1 contains headers.
    For r = 2 To lastRow + 1
        If IsEmpty(ws.Cells(r, "A").Value) Then
            firstBlank = r
            Exit For
        End If
    Next r
End Sub

The loop checks through one row past the last ID, so it can find the append position when there are no gaps. If column A is entirely blank, however, lastRow resolves to 1 and the loop checks row 2. Adjust the start row if your layout differs. Also decide what “empty” should mean for your data: a formula returning "" is not an empty cell to IsEmpty, while a visually blank cell may still contain a formula, spaces, or formatting.

Excel tables and other options

If the records are stored in an Excel Table, consider adding a row to the table rather than calculating a worksheet row independently. A table can expand to include a newly entered row, and its structured references and formatting are designed to follow the table’s records. In VBA, a table is represented by a ListObject; its ListRows.Add method adds a data row. This is often more maintainable than relying on a particular worksheet column, especially when the table is sorted or moved.

For a simple fixed-range list, End(xlUp) is short and easy to understand. Use Find when “last used row” means content across the whole worksheet. Use a loop when the requirement is the first blank within a key column. Use a table’s row-add method when the data is already organized as a table. These methods answer different questions rather than being interchangeable versions of one formula.

Common problems and safeguards

  • Wrong sheet: qualify the range with a worksheet variable instead of relying on the active sheet.
  • Wrong reference column: choose a field that is present in every record, or the result may stop too early.
  • Headers or empty data: set the first data row deliberately, especially when the sheet may start empty.
  • Formulas that look blank: a formula is still content to many row-finding methods. Decide whether formulas should count as occupied.
  • Formatting mistaken for data: formatting alone does not make a cell nonempty for the End(xlUp) approach, though other worksheet “used range” checks can be affected by formatting history.
  • Worksheet limit: Excel worksheets have a maximum row count. If the last occupied row is already the final row, adding one gives an invalid destination; check for that before writing.

Macros can also overwrite data if the chosen column is not a dependable record marker. For important workbooks, test the code on a copy containing representative cases: a normal list, a gap, an empty list, and formulas that display blank. Save a separate copy before running it, because VBA changes are not reversed by Ctrl+Z.

Frequently Asked Questions

What is the VBA code for the next empty row?
For appending below records marked in column A, use ws.Cells(ws.Rows.Count, "A").End(xlUp).Row + 1. Set ws to the intended worksheet and choose a column that is filled for every record.

How do I find the first blank row instead of the last row?
Scan the key column from the first data row and stop at the first empty cell. The append expression skips internal blanks; it returns the row after the last occupied cell, even when there is a gap above it.

Does VBA count a formula that displays a blank as empty?
Usually not with these approaches. End(xlUp) can land on a cell containing a formula even if its displayed result is an empty string, and IsEmpty returns False for a formula cell. Define whether formulas count as occupied before choosing the method.

Can I run a VBA macro in Excel for the web?
No. Excel for the web can open and edit many macro-enabled workbooks, but it cannot create, edit, or run VBA macros. Use desktop Excel for Windows or Mac to run this code.

Why does my macro put the new value in the wrong sheet?
A range reference without a worksheet qualifier often refers to the active sheet. Assign the intended sheet to a variable and use that variable for both the row search and the write operation.

Can Ctrl+Z undo a row added by a macro?
No. Running VBA clears Excel’s normal undo history, so macro changes generally cannot be reversed with Ctrl+Z. Save a copy before running code that changes workbook data.

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.