Excel VBA: Find the Last Column in a Sheet or Range

To find the last nonempty column in a specific row, start at the worksheet’s last column and move left with Excel VBA’s End method. To find the last column containing a value or formula anywhere on a worksheet, use the Find method with a column-based search; the right choice depends on what “last column” means in your data.

What “last column” means in VBA

There is no single last-column answer that fits every worksheet. A row may end in column H while another row continues to column M. A worksheet-wide search asks for the rightmost cell with content anywhere, while a table-based search may mean the last column in the table’s defined structure, even if some cells in that column are blank.

It also helps to distinguish content from appearance. A cell can have a fill color, border, or number format without containing a value or formula. Most last-content methods ignore formatting-only cells. Conversely, a formula that displays an empty string is still a formula, so a method that checks formulas can treat that cell as occupied.

One row: Find the rightmost populated cell in that row.
Whole worksheet: Find the rightmost column containing content in any row.
Table: Get the table’s defined column count or its right edge, rather than searching for nonblank cells.

Find the last column in a particular row

For a row with data across some columns, the common VBA pattern is to begin at the far-right worksheet cell in that row and use End with xlToLeft. Excel 365 worksheets have 16,384 columns, ending at XFD, and VBA can use the worksheet’s Columns.Count so the code does not need a hard-coded column letter.

This complete example checks whether row 5 has anything in it before searching. It prints the column number in the Immediate window; 1 means column A, 2 means B, and so on. A result of 0 means the row is empty according to CountA.

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

    ' Use the worksheet that contains the data.
    Set ws = ThisWorkbook.Worksheets("Data")
    targetRow = 5

    ' Return zero for an empty row; otherwise search from the right.
    If Application.WorksheetFunction.CountA(ws.Rows(targetRow)) = 0 Then
        lastCol = 0
    Else
        lastCol = ws.Cells(targetRow, ws.Columns.Count).End(xlToLeft).Column
    End If

    Debug.Print lastCol
End Sub

Change Data and targetRow to match the workbook and row. CountA counts constants, errors, and formulas, including formulas whose displayed result is an empty string. That makes the check consistent with End, which can stop at a formula cell even when it looks blank. If the intended definition is “last cell with a visible, nonempty result,” this pattern may need a different test.

The empty-row check matters because End(xlToLeft) from the far-right edge of a completely empty row returns column 1. Without a check, code can report A as the last column when there is no data at all.

Find the rightmost content anywhere on a worksheet

When rows have different lengths and you need the last column used anywhere on the sheet, search all cells with Find. Set SearchOrder to xlByColumns and SearchDirection to xlPrevious so the search works backward through columns. Check for Nothing because Find returns no Range when the worksheet contains no matching content.

Sub ShowLastColumnOnSheet()
    Dim ws As Worksheet
    Dim foundCell As Range
    Dim lastCol As Long

    ' Point to the worksheet to inspect.
    Set ws = ThisWorkbook.Worksheets("Data")

    ' Search formulas and values, moving backward by column.
    Set foundCell = ws.Cells.Find(What:="*", _
        After:=ws.Cells(1, 1), _
        LookIn:=xlFormulas, _
        LookAt:=xlPart, _
        SearchOrder:=xlByColumns, _
        SearchDirection:=xlPrevious, _
        MatchCase:=False, _
        SearchFormat:=False)

    If foundCell Is Nothing Then
        lastCol = 0
    Else
        lastCol = foundCell.Column
    End If

    Debug.Print lastCol
End Sub

Using xlByColumns is important: this asks Excel to order the search by columns, so the final match is in the rightmost matching column. The found cell’s row is not necessarily the last data row, and its column number is what this task needs. The search includes formulas because LookIn is xlFormulas; a formula that returns an empty string can therefore count. It does not treat formatting alone as content.

How the methods differ

End(xlToLeft) behaves like navigating from the right edge of one row toward the left. It is concise and usually the clearest option when the data is organized in a known row, such as a header row or one record. It does not answer a worksheet-wide question unless you separately inspect all relevant rows.

Find searches a range and can locate content across many rows in one operation. Its settings matter: Excel remembers some Find options from previous searches, including searches made through the Find dialog. Supplying LookIn, LookAt, SearchOrder, SearchDirection, MatchCase, and SearchFormat explicitly makes the VBA search more predictable.

UsedRange is another property people often try. Its right edge can be calculated from UsedRange.Column plus UsedRange.Columns.Count minus one, but UsedRange describes Excel’s used area, not necessarily the rightmost cell with meaningful content. Formatting applied far to the right can expand it, and deleting content may not immediately make the reported area match a clean data boundary. Use it when the used area itself is relevant, not as a guaranteed content search.

Choose the method that matches the data

Pick the definition before writing the macro. A method can be technically correct and still return the wrong answer if it answers a different question.

A fixed header row defines the data width: Use End(xlToLeft) on that row. This is common when row 1 contains field names.
Any row may extend the data farther right: Use Find across the worksheet or a clearly defined range.
The data is an Excel table: Use the table’s ListColumns.Count for the number of defined columns. To get its rightmost worksheet column, add the count to the table range’s starting column and subtract one.
You only want to inspect a known block: Search that range rather than every cell on the worksheet. This avoids counting unrelated notes elsewhere.

A table’s last defined column is not always its last populated column. For example, a table can include a blank “Comments” field at the right edge. ListColumns.Count still includes it, which is useful for a table schema but different from asking where the last nonblank entry occurs.

Common edge cases and mistakes

The most frequent surprise is an apparently blank cell that contains a formula. A formula such as one returning an empty string looks empty in the grid, but it remains a formula. End and a Find search looking in formulas can count it. If your rule is based on displayed results instead, consider a search looking in values, or write a deliberate value-checking loop for the exact range. Be aware that cells containing a space are not truly empty either.

Other details can affect the result:

Empty rows: End from the far right returns column A unless you check for an empty row first.
Formatting without content: A colored or bordered cell alone is not normally found by the content search.
Hidden columns: A hidden column can still contain content; hiding it does not make the value disappear from a worksheet-wide content search.
Wrong worksheet: Unqualified references such as Cells or Columns use the active sheet. Qualify them with a worksheet variable to avoid inspecting a different sheet after the user changes tabs.
Data outside the intended block: A title, note, or stray entry far to the right can make the global last-column result larger than the table you meant to measure.

For a known region, limiting the Find range to that region is often the cleanest way to exclude notes and unrelated cells. Decide whether headers, formulas, hidden cells, and spaces count as content, then use a method that follows that definition consistently.

Use explicit workbook and worksheet references

In the examples, ThisWorkbook means the workbook containing the VBA code. That is appropriate when the macro is stored in the workbook being inspected. If the code is stored in PERSONAL.XLSB or another workbook, ThisWorkbook refers to that code workbook, not necessarily the workbook currently on screen. In that situation, set the worksheet variable from the intended workbook explicitly rather than relying on whichever sheet happens to be active.

Likewise, use ws.Columns.Count and ws.Cells instead of unqualified Columns.Count and Cells. This keeps the starting point and the search on the same worksheet. The worksheet name must exist exactly as written; otherwise, VBA raises an error when it tries to set the worksheet variable.

Running the macro safely

On Windows, open the VBA editor with Alt+F11, insert a standard module, and paste a complete procedure into it. In Excel for Mac, the VBA editor shortcut is usually Option+F11; some keyboards require Fn as well. Excel for the web does not run VBA macros, so use the desktop Excel application to edit or run this code.

Save the file in a macro-enabled format such as .xlsm if it needs to retain VBA. Before running any macro, save a copy of the workbook: macros cannot be undone with Ctrl+Z. The examples above only print a number and do not change worksheet data, but keeping a copy is a sensible precaution whenever running VBA.

Frequently Asked Questions

How do I get the last column letter instead of its number?

The examples return a column number because it is convenient for later VBA operations. To convert a number to a letter, use the worksheet’s Cells property and read its Address with the row portion removed. For most macros, keeping the numeric index is simpler and avoids extra conversion.

Why does VBA return 1 when the row is blank?

Starting at the far-right cell and moving left on an empty row reaches the first column. Check the row with CountA before calling End, and choose whether formulas returning empty strings should count as populated.

Does Find count formulas that display nothing?

With LookIn set to xlFormulas, it searches formula contents, so a formula returning an empty string can be found. Searching values instead may better match visible results, but test it against the workbook’s formulas and intended definition of “nonempty.”

Can I use this approach to find the last row?

Yes. The matching pattern is to start at the bottom of a chosen column and use End(xlUp), with an empty-column check if needed. For a worksheet-wide last row, use Find with SearchOrder set to xlByRows and SearchDirection set to xlPrevious.

What is the last possible Excel column?

In current Microsoft 365 workbooks, the final column is XFD, which is column number 16,384. Using the worksheet’s Columns.Count in VBA is more adaptable than embedding that number in code, especially when working with older workbook formats.

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.