Excel VBA: Find the Last Column With Data in a Row

To find the last column containing data in a particular row, use ws.Cells(rowNumber, ws.Columns.Count).End(xlToLeft).Column. For the last column containing data anywhere on a worksheet, use Find with a search ordered by columns; the right choice depends on whether you mean one row or the whole sheet.

Find the last column in one row

The common VBA approach starts at the far-right column of the row and moves left to the first occupied cell. Excel’s End(xlToLeft) behaves like pressing Ctrl+Left from the last column: it navigates to the edge of the current block of non-empty cells. Because the search begins at the worksheet’s right edge, the resulting column is the rightmost occupied one in that row, even if there are blank cells between entries.

For example, if row 2 contains headings in A2, B2, and F2, the result is 6, the column number for F. The result is a number, not a letter. This is useful when a macro needs to build a range, add data after existing entries, or determine how many columns of a row to process.

Sub ShowLastColumnInRow()
    Dim ws As Worksheet
    Dim targetRow As Long
    Dim lastCol As Long

    Set ws = ThisWorkbook.Worksheets("Data")
    targetRow = 2

    ' Stop if this row has no constants or formulas.
    If Application.WorksheetFunction.CountA(ws.Rows(targetRow)) = 0 Then
        MsgBox "Row " & targetRow & " is empty."
        Exit Sub
    End If

    lastCol = ws.Cells(targetRow, ws.Columns.Count).End(xlToLeft).Column
    MsgBox "Last occupied column number: " & lastCol
End Sub

Change "Data" to the worksheet’s tab name and set targetRow to the row you want to inspect. The empty-row check matters: without it, starting from the right edge of a completely empty row reaches column A, so the expression returns 1 even though there is no data. Save a copy of the workbook before running a macro. VBA actions cannot be undone with Ctrl+Z.

How End(xlToLeft) works

ws.Columns.Count supplies the number of columns in that worksheet, so the starting cell is in its last column. In current Excel worksheets, that is column XFD, number 16,384. The End method then moves left through the row to locate the edge of the occupied region. Using the worksheet’s own column count is preferable to hard-coding 16,384 because it makes the code clearer and less dependent on a particular worksheet size.

Internal gaps do not prevent this method from finding the rightmost entry. If A5 and D5 have values but B5 and C5 are blank, starting at the last column and moving left still finds D5. However, the method is for a single row. It does not answer which column is furthest right anywhere across all rows, and it does not tell you how many occupied cells are in the row.

For downstream code, you can use the returned number directly, or convert it to a cell reference. For instance, ws.Cells(targetRow, lastCol) refers to the rightmost occupied cell in that row. Be careful when the row is empty: testing CountA first avoids treating A as a real result.

Find the last used column anywhere on a sheet

When data may appear on any row, use the worksheet’s Find method rather than checking just one row. This example searches for the last cell with a constant or formula and reports its column. It also handles a sheet with no matching cells.

Sub ShowLastColumnOnSheet()
    Dim ws As Worksheet
    Dim lastCell As Range

    Set ws = ThisWorkbook.Worksheets("Data")

    ' Search formulas and constants, checking all relevant Find options.
    Set lastCell = ws.Cells.Find(What:="*", _
        After:=ws.Cells(1, 1), _
        LookIn:=xlFormulas, _
        LookAt:=xlPart, _
        SearchOrder:=xlByColumns, _
        SearchDirection:=xlPrevious, _
        MatchCase:=False, _
        SearchFormat:=False)

    If lastCell Is Nothing Then
        MsgBox "No constants or formulas were found."
    Else
        MsgBox "Last used column number: " & lastCell.Column
    End If
End Sub

Here, SearchOrder:=xlByColumns and SearchDirection:=xlPrevious direct Excel to return the last match in the column-wise search order. LookIn:=xlFormulas includes formulas even when their displayed results are blank. All important search settings are specified because Excel’s Find dialog can retain settings from earlier searches, and omitted arguments may inherit those settings. This code considers cells with formulas or constants; it is not intended to find formatting by itself.

Choose the method for your data layout

Decide what “last column with data” means before choosing a line of VBA. The relevant distinction is the search area, not merely the code style:

One known row: use Cells(rowNumber, Columns.Count).End(xlToLeft).Column. This is concise and suitable for a header row or one record.
Any row on the worksheet: use Find with SearchOrder:=xlByColumns and check whether the result is Nothing.
A defined Excel table: use the table’s range or a named table column when the question is specifically about the table. This avoids unrelated notes or cells elsewhere on the sheet.
A fixed data region: limit the search to that region if content outside it should not count. A whole-sheet search can include titles, notes, or helper formulas.

These methods answer a “rightmost cell” question. If instead you need the number of fields in a structured table, use the table’s column count; if you need the last row, search vertically or use a last-row method. Similar-sounding tasks can return different answers when there are gaps, notes, or more than one data region.

Formulas that display blank and other edge cases

A cell containing a formula such as =IF(A1="","",A1) is not truly empty: it contains a formula, even if the result looks blank. End(xlToLeft) generally treats that formula cell as occupied, and the example using Find with LookIn:=xlFormulas deliberately includes it. This is often the right behavior when a formula is part of the worksheet’s structure.

If “data” means only a nonblank displayed result, that is a different requirement. In the Find example, changing LookIn to xlValues searches displayed values rather than formula text, but the result depends on what the formulas currently display. Test the approach against formulas returning empty strings, errors, and numbers in your actual layout. A cell containing a space is also not empty, even though it may look blank; trim or clean such entries if they should not count.

Formatting alone is another common source of confusion. A cell can have a fill color, border, or number format without containing a value or formula. The examples above search for content, not formatting-only cells. Conversely, stray constants or formulas outside the visible table may make a whole-sheet search report a column farther right than expected.

Why UsedRange can give a surprising result

UsedRange returns Excel’s used range for a worksheet, but it is not always a precise synonym for “cells that currently contain data.” Formatting or past edits can extend the recorded used area beyond the visible dataset. As a result, reading the last column of UsedRange can give a larger column number than a content search.

It can still be useful when a macro intentionally needs the worksheet’s used area, including more than just values. For a dependable last-content-column result, Find is usually clearer because you can specify whether to search formulas or values. For one known row, End(xlToLeft) is simpler. If a worksheet has accumulated excess formatting, deleting unused rows or columns and saving may help Excel reset the used range, but do not delete anything until you have confirmed it is outside the required data.

Common mistakes to avoid

Using row 1 without checking: a heading row is convenient, but it may not extend as far as data rows below it. Choose the row that matches the task.
Forgetting the empty-row case: End(xlToLeft) can return 1 for a blank row. Check for content if that result would be misleading.
Using the wrong worksheet reference: qualify both Cells and Columns.Count with the target worksheet. Unqualified references can point to the active sheet instead.
Leaving Find options implicit: Excel may remember Find settings. Specify the search options in code so the macro behaves consistently.
Assuming the result is a letter: .Column returns a numeric index. Use the index directly with Cells, or convert it only if a letter is specifically needed.

For maintainability, keep the worksheet in a variable such as ws, use descriptive row variables, and make the scope of the search obvious. In a workbook that other people use, also decide how the macro should behave when the target sheet is missing or the input row is invalid.

Frequently Asked Questions

What is the shortest VBA code to find the last column in row 1?
Use lastCol = Cells(1, Columns.Count).End(xlToLeft).Column in a procedure where the intended worksheet is active. For more reliable code, qualify both references with a worksheet variable, and check whether row 1 is empty.

How do I find the last column with data across all rows?
Use Cells.Find with SearchOrder:=xlByColumns and SearchDirection:=xlPrevious, as shown above. Check whether the returned range is Nothing before reading its Column property.

Does End(xlToLeft) count formulas that return an empty string?
Yes, a formula is still present even when its displayed result is an empty string, so it can be treated as occupied. If you want to count only visible, nonblank results, use a method designed around displayed values and verify its behavior with your formulas.

Why does my macro return column 1 when the row is empty?
Starting from the far-right cell and moving left on an empty row reaches the first column. Check the row first, for example with CountA, and handle the empty case separately.

Can Excel for the web run this VBA macro?
No. Excel for the web can open a workbook containing VBA, but it cannot create, run, or edit VBA macros. Use the desktop Excel app for VBA; the basic row-search logic is available in desktop Excel for Windows and Mac.

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.