Excel VBA: How to Find the Last Used Column in a Row

VBA & macros · Updated November 19, 2026

In Excel VBA, find the last used column in a row by starting at the worksheet’s final column and moving left: ws.Cells(rowNum, ws.Columns.Count).End(xlToLeft).Column. If the row might be empty, check it first; otherwise, this method returns column 1 for a blank row, which can be mistaken for real data in column A.

The basic End(xlToLeft) method

End(xlToLeft) is the VBA equivalent of moving left from a cell with Excel’s End key. Starting at the far-right edge of a row lets the code find the rightmost cell Excel treats as occupied. The returned Column property is a number: A is 1, B is 2, and XFD, the final Excel column, is 16,384.

For example, with a worksheet named Data and values in B4, C4, and F4, the last occupied column in row 4 is F, so the code returns 6. A worksheet reference is important: it ensures the search uses the intended sheet, rather than whichever sheet happens to be active.

Public Function LastUsedColumnInRow(ByVal ws As Worksheet, ByVal rowNum As Long) As Long
    Dim lastCol As Long

    ' Reject a missing worksheet or a row outside this worksheet.
    If ws Is Nothing Then Err.Raise vbObjectError + 1000, , "Worksheet is required."
    If rowNum < 1 Or rowNum > ws.Rows.Count Then
        Err.Raise vbObjectError + 1001, , "Row number is outside the worksheet."
    End If

    ' Return 0 for a row with no constants or formulas.
    If Application.CountA(ws.Rows(rowNum)) = 0 Then
        LastUsedColumnInRow = 0
    Else
        lastCol = ws.Cells(rowNum, ws.Columns.Count).End(xlToLeft).Column
        LastUsedColumnInRow = lastCol
    End If
End Function

This function returns 0 when the row is empty, which is a deliberate convention: there is no last occupied column to report. If you prefer a different result for an empty row, change that branch. The function uses Long for the row and column numbers, which can represent every row and column in a modern Excel worksheet.

How to add and call the function

In Excel for Microsoft 365 on Windows, open the Visual Basic Editor with Alt+F11. Choose Insert > Module, paste the function into the module, and call it from another procedure. On a Mac, open the Visual Basic Editor using Option+F11 (some keyboards require Fn as well), then insert a module in the same way. Excel for the web cannot create, edit, or run VBA macros; use the desktop app.

For example, the following procedure checks row 4 on the Data worksheet and displays the column number. Save the workbook as a macro-enabled .xlsm file if you need to keep its VBA code.

Sub ShowLastColumn()
    Dim ws As Worksheet
    Dim lastCol As Long

    ' Set the worksheet explicitly rather than relying on the active sheet.
    Set ws = ThisWorkbook.Worksheets("Data")
    lastCol = LastUsedColumnInRow(ws, 4)

    MsgBox "Last used column number: " & lastCol
End Sub

Save a copy of the workbook before running a macro. This example only displays a result, but VBA actions that change cells cannot be reversed with Excel’s Ctrl+Z undo command. On Mac, the usual undo shortcut is Command+Z, but it does not undo changes made by a macro either.

What counts as an occupied cell?

The method is about cell contents, not whether a cell looks filled. A number, text value, date, error value, or formula can make a cell count as occupied. Cell formatting alone—such as a fill color, border, or number format—does not ordinarily make an otherwise empty cell the last used cell for this search.

The blank-row check uses CountA. It returns zero when a row contains no constants or formulas. A cell containing a formula that displays an empty string, such as a formula whose result is "", is still counted because the formula exists. The search likewise treats a formula cell as occupied even if its displayed result looks blank. This is often the desired meaning of “last used”: the last cell containing something that may calculate or hold data.

If your definition is instead “the last cell with a visible, nonblank result,” you need to test cell values rather than simply locate formulas. That distinction matters in sheets where formulas are pre-filled far to the right but currently return empty strings.

Choosing between End and Find

For a straightforward rightmost-content check on one row, End(xlToLeft) is concise and easy to understand. Another common technique uses the Range.Find method with a backward search. Find can be useful when a procedure already uses it or needs to search a particular range, but its options deserve care: Excel remembers some Find settings from earlier searches, so VBA code should specify the search arguments it relies on.

Do not switch to UsedRange just to find a row’s last column. UsedRange describes a worksheet area, not the rightmost occupied cell in a particular row; it can include cells used elsewhere on the sheet and may reflect previous editing or formatting. Similarly, CurrentRegion finds a contiguous block bounded by blank rows or columns. It is useful for a table-like data block, but it can stop at a gap even when the row has data farther to the right.

  • Use End(xlToLeft) when you want the rightmost occupied cell in a specified row.
  • Use a bounded range when only a known section of the row should count.
  • Use table-specific logic when the question is about the final table field rather than the worksheet’s final occupied cell.

Limit the search to a known range

By default, the code searches the entire worksheet width. That is appropriate when any column could contain relevant data, but some sheets reserve far-right columns for notes, checks, or other content that should not count. In that situation, start at the right edge of the permitted range instead. For example, if the data is restricted to columns A through M, use column 13 as the starting point:

Dim lastCol As Long

' Search only columns A through M on row 4.
lastCol = Worksheets("Data").Cells(4, 13).End(xlToLeft).Column

This bounded version can return a column outside the intended data area if the allowed range is empty: starting in M and moving left reaches A. Add a check for the range you care about if an empty range needs a distinct result. Also ensure that values to the right of the allowed range are intentionally excluded; narrowing the search changes the question the code answers.

Common problems and edge cases

  • Blank rows return 1 without a check. The unguarded End expression lands in column A when no occupied cell exists to its left. Check for an empty row first if 1 must mean actual data in A.
  • The wrong worksheet is searched. Qualify both the worksheet and the row. Unqualified Cells can refer to the active sheet, which may not be the sheet your code is processing.
  • A formula looks blank but is counted. A formula returning an empty string is still a formula. Decide whether you mean “contains a formula” or “displays a nonblank value.”
  • Formatting is mistaken for data. A colored or bordered cell may look used but does not, by itself, establish the last content column in this approach.
  • A gap is mistaken for the end. Starting from the worksheet’s final column searches back across gaps. Starting from a nearer cell can stop at a different boundary, so choose the starting point to match the scope you need.

Merged cells are another reason to check the sheet layout. A merged area behaves as a single cell for many operations, and its value is stored in the upper-left cell of the merged area. If your data row uses merged cells, test the behavior against that layout rather than assuming every column inside the merge acts like a separate occupied cell.

Getting the column letter or cell address

The function returns a number because column numbers are convenient for later VBA operations. To get the letter, ask Excel to convert that number into a cell address. With lastCol holding a valid column number and row 4 as the target row, this expression gives an address such as F4:

Dim address As String

' Convert the column number into a row-specific cell address.
address = ws.Cells(4, lastCol).Address(False, False)

For just the column letters, remove the row number from the address:

Dim columnLetter As String

' The address for row 1 contains only the column letter after this step.
columnLetter = Replace(ws.Cells(1, lastCol).Address(False, False), "1", "")

That simple replacement works for row 1’s address, including multi-letter columns such as AA. Before converting an empty-row result of 0 into an address, handle it separately: column 0 is not a valid worksheet column.

Frequently Asked Questions

What does VBA return if the row is empty?
The direct End(xlToLeft) expression returns 1, the number for column A. The function above checks CountA first and returns 0 for an empty row so that result is not confused with data in A.

Does this find the last column with a formula?
Yes. A formula counts as cell content even when its displayed result is an empty string. If formulas should not count unless they display a value, use a value-based test instead.

How do I get the last column in the active row?
Use ActiveCell.Row for the row number, but still qualify the worksheet. For example, pass ActiveSheet and ActiveCell.Row to the function when the active sheet is intentionally the target.

Does it find the last column with formatting?
No. This method looks for contents such as values and formulas, not formatting by itself. If formatting is what you need to inspect, that is a different search and should be based on the specific formatting property.

Can I use VBA in Excel for the web?
No. Excel for the web can open a macro-enabled workbook, but it cannot create, edit, or run VBA macros. Use Excel’s desktop app for the code in this guide.

Which approach should you use?

For most VBA tasks, start at the last worksheet column and use End(xlToLeft), with an explicit worksheet and a defined policy for empty rows. It is a good fit for finding the rightmost cell containing a value or formula in one row. Restrict the starting column when your data has a known boundary, and use a different test when “used” means visible result, table field, or formatting rather than underlying cell content. Keeping that definition clear prevents the most common error: getting a valid column number that answers a different question than the one your code intended.

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.