To find the next empty row after the data in an Excel column, use End(xlUp) from the bottom of that column, then add 1. For the last row containing anything anywhere on a worksheet, use Range.Find instead; the right method depends on what you mean by “last empty row.”
What “last empty row” usually means
An Excel worksheet contains 1,048,576 rows, so there is no single final empty row: after any data, many rows remain blank. In practice, people asking for the “last empty row” usually want the next row available for new data, or the last row that currently contains data so they can calculate what comes next. Those are related but different results.
It also matters whether you mean a blank in a particular column, a completely empty row across the whole sheet, or the first gap inside a list. A row can be empty in column A while containing a value in column B. Similarly, a cell with a formula that displays an empty string may look blank but still contain a formula. Decide what “empty” means for your task before choosing code.
- Next row after a list in one column: Find the last populated cell in the key column and add 1.
- Last row with content anywhere: Search the worksheet for the final cell containing a constant or formula.
- First blank within a list: Examine the rows in the list’s expected range and locate a gap; do not simply add 1 to the last used row.
Find the next empty row in a key column
This is the common choice for a table where one column, such as an ID or name column, is reliably filled on every record. The code below treats column A as that key column. It returns row 1 if column A is entirely empty; otherwise, it reports the first row after the last nonempty cell in A.
Sub FindNextEmptyRowInColumnA()
Dim ws As Worksheet
Dim lastRow As Long
' Change Data to the worksheet name in your workbook.
Set ws = ThisWorkbook.Worksheets("Data")
If Application.CountA(ws.Columns("A")) = 0 Then
lastRow = 0
Else
lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
End If
If lastRow = ws.Rows.Count Then
MsgBox "Column A has no row available after its last entry."
Else
MsgBox "Next empty row in column A: " & (lastRow + 1)
End If
End Sub
ws.Rows.Count gets the final row number for that worksheet, and End(xlUp) moves upward from the bottom of column A to its last nonempty cell. The empty-column check is important: without it, starting at the bottom and moving upward can lead to row 1 even when the column contains nothing, which makes it easy to confuse the last data row with the next available row. With a header in A1 and no records yet, this example reports row 2.
Change both references to column A if your key column is different. Use a column that is populated for every record; if some records have a blank key, this method may return a row above other data elsewhere on the sheet.
Find the last row containing content anywhere
When data may appear in several columns, search the whole worksheet. Find can locate the last cell with a constant or formula, and setting its search options explicitly makes the result less dependent on settings left over from Excel’s Find dialog.
Sub FindLastContentRowOnSheet()
Dim ws As Worksheet
Dim lastCell As Range
' Change Data to the worksheet name in your workbook.
Set ws = ThisWorkbook.Worksheets("Data")
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
MsgBox "The worksheet contains no values or formulas."
Else
MsgBox "Last row with content: " & lastCell.Row
End If
End Sub
Here, SearchOrder:=xlByRows makes the search identify the final row containing a match, and SearchDirection:=xlPrevious searches backward from the specified starting point. The result is the row number of the last matching cell, not the next empty row; add 1 when you specifically need a row after all content. For example, if the final content is in row 18, the last used row is 18 and the next row is 19.
This search includes formulas because LookIn:=xlFormulas searches formula contents. A formula such as one returning an empty string can therefore count as content even though the cell appears blank. If you want to ignore formulas that display nothing, the rule needs to be based on displayed values instead, and the search should be adapted to that requirement.
Choose the method that matches your layout
Use the key-column approach for a well-formed list with a dependable ID or other required field. Use the worksheet-wide search when records can have blanks in the key column, or when relevant content may extend across different columns. Neither method can infer which cells belong to your dataset: an old note far below the table is still content and may affect the result.
For a formal Excel table, another sensible approach is to add records through the table’s ListRows.Add method rather than calculating a worksheet row yourself. That lets Excel expand the table and carry its structure forward. It is especially useful when the table has filters, calculated columns, or formatting that should apply to new records. Row-finding code remains useful when the worksheet is not organized as a table or when a process specifically needs a worksheet row number.
Find a blank row inside a list
A blank in the middle is not the same as the next row after the list. Suppose records occupy rows 2 through 20, but row 8 is empty. The key-column method still finds row 21, because it looks for the last populated cell. It does not fill the gap at row 8.
If you need the first truly empty cell within a known range, loop from the first data row to the last data row and test each cell in the key column. For example, you could set a starting row of 2 and use the last data row found by the key-column method as the loop limit. A test with IsEmpty identifies cells that contain no value or formula. It does not treat a formula returning an empty string as truly empty. Choose a different test if “looks blank” is your intended definition, and decide what to do if there are no gaps: typically, use the row after the last record.
Common problems and edge cases
- Blank rows in the middle:
End(xlUp)finds the last populated cell, not the first gap. Search the list range when gaps matter. - Formulas that display nothing: A formula is still present. The result may count as used even when the cell looks empty.
- Formatting below the data: Formatting alone is not the same as a value or formula. A search for
*will not treat formatting by itself as matching content. - Content in another column: A key-column search ignores other columns. Use a worksheet-wide search if any column can determine the final row.
- Wrong worksheet reference:
ThisWorkbookrefers to the workbook containing the VBA code. If the macro is stored in another workbook, make sure that is the workbook you intend to inspect. - Sheet-name mismatch: Replace
Datawith the exact worksheet tab name. A nonexistent name causes a run-time error.
There is a related misconception that Excel’s displayed “used range” always equals the last row with meaningful data. It can be affected by past editing and formatting, and it is not the same question as finding the last value in a chosen column. For dependable row logic, explicitly search the cells or column that define your data rather than assuming the worksheet’s apparent boundary is the data boundary.
Run the VBA safely in Excel
In Excel for Microsoft 365 on Windows, open the Visual Basic Editor with Alt+F11, choose Insert > Module, and paste a procedure into the module. On a Mac, the editor is available through Tools > Macro > Visual Basic Editor; Option+F11 may open it, though some keyboards require the Fn key as well. If the Developer tab is not shown, enable it through Excel’s ribbon preferences or options.
Run a selected procedure from the editor, or use Excel’s macro interface to choose its name. Save a copy of the workbook before running a macro. VBA macros cannot be undone with Ctrl+Z on Windows; on Mac, Command+Z is the usual Undo shortcut, but it does not reverse changes made by a macro either. The examples above only display a message, but a macro that writes or clears cells can make changes that require restoring a saved copy.
If you want to retain VBA, save the workbook in a macro-enabled format such as .xlsm. Only enable macros in files you trust. Excel for the web can open a workbook containing VBA, but it cannot create, run, or edit VBA macros; use the desktop Excel application for these procedures.
Frequently Asked Questions
How do I get the next empty row in Excel VBA?
Find the last nonempty cell in a dependable key column with Cells(Rows.Count, column).End(xlUp).Row, then add 1. Check separately whether the column is completely empty so you can handle a new list correctly.
Does End(xlUp) find the first blank row?
No. It finds the last nonempty cell when starting from the bottom of a column. Adding 1 gives the row immediately after that cell, but it does not locate empty gaps above it.
How can I find the last used row across all columns?
Use Find with a wildcard, search by rows, and search backward. Check whether the returned range is Nothing before reading its row number, because an empty worksheet has no match.
Will formulas that return an empty string count as empty?
Not when searching with LookIn:=xlFormulas; the formula itself is content. For a key-column test, End(xlUp) also recognizes a cell containing a formula, even if its displayed result is blank.
Why does my macro return a row below the visible table?
There may be a value, formula, or note elsewhere in the searched column or worksheet. Check the exact search area and decide whether the macro should follow one key column, all worksheet content, or a defined table range.
Practical rule of thumb
For a conventional list, start with the key-column method and treat its result plus one as the next row. Switch to a worksheet-wide Find when data may extend beyond that column. If you need to reuse blank gaps, search within the list instead. Stating the range and the meaning of “empty” is what makes the result reliable—not simply using a particular VBA command.
Related
- Excel Spreadsheet Keyboard Shortcuts: A Practical Guide
- Excel Data Validation Lists: Create and Manage Dropdowns
- Excel Shuffle List of Names: 10 Easy Methods
Try it: the Column Letter to Number: aA is 27, XFD is 16,384. Converts both ways.
More in VBA & macros
- Excel VBA: Save a Workbook as a New File with SaveAsTo save an Excel workbook as a new file with VBA, use the workbook’s SaveAs method and supply…
- Excel VBA Default Parameter Values and Optional ArgumentsIn Excel VBA, give a parameter a default by declaring it Optional and assigning a value in the…
- Excel VBA Macro to Copy Data Between SheetsTo copy data between worksheets with an Excel VBA macro, assign the source range’s values to a destination…
- VBA Excel Open and Save As Dialog Box: How It WorksIn Excel VBA, use Application.GetOpenFilename to display a file-selection dialog and Application.GetSaveAsFilename to ask where a file should…
- VBA Check If a Worksheet Is ProtectedIn VBA, check a worksheet’s ProtectContents property: Worksheets("Data").ProtectContents returns True when that worksheet’s cell contents are protected and…
- Excel VBA: Open a Text File for WritingIn Excel VBA, use Open filePath For Output As #fileNo to create a text file or replace its…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.