VBA Find the Last Column in Excel

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

What “Last Column” Means

Excel has 16,384 columns, from A through XFD, but a worksheet does not have one universal last used column. A row of headers might end in column H while data lower down extends to column K. Formatting could also be applied as far as column Z even though the actual records stop at K. Before choosing VBA, decide whether you need the last occupied column in one row, the rightmost cell with a value or formula anywhere on the sheet, or the edge of a defined table.

These are different questions, so they can produce different answers. For example, if row 1 has headers through H and a note in K20, a row-1 method returns 8 (H), while a worksheet-wide search returns 11 (K). A method that reports the last column in the worksheet’s used range might return an even larger number if cells beyond K have formatting.

Find the Last Nonempty Column in One Row

Use End(xlToLeft) when a particular row defines the data boundary, such as a header row or a row of totals. The expression below checks row 1 on a worksheet named Data and returns a column number, not a column letter.

With the headers in row 1 of the Data worksheet, this macro returns the number of the rightmost nonempty cell in that row. Save a copy of the workbook before running a macro; macros cannot be undone with Ctrl+Z.

Sub FindLastColumnInHeaderRow()
    Dim ws As Worksheet
    Dim lastCol As Long
    Dim headerRow As Long

    ' Use the worksheet containing the headers.
    Set ws = ThisWorkbook.Worksheets("Data")
    headerRow = 1

    ' Start at the far-right cell in the row and move left.
    lastCol = ws.Cells(headerRow, ws.Columns.Count).End(xlToLeft).Column

    MsgBox "Last column number: " & lastCol
End Sub

If the last header is in H1, lastCol is 8. To check another row, change headerRow. To use the result in later code, you can refer to ws.Cells(headerRow, lastCol) for the cell or ws.Columns(lastCol) for the whole column.

This approach is quick and easy to understand, but it only examines the row you specify. It does not discover cells below that row, and a blank cell at the end of a row does not cause a problem: the search moves left to the nearest nonempty cell.

Find the Last Column Used Anywhere on a Worksheet

When records are uneven or you need to check the entire sheet, use Find. Set the search order to columns and the direction to previous so Excel searches backward through the worksheet’s cells. The code below stores the matching cell in a Range variable and reads its column number.

This version treats formulas as content, including a formula whose displayed result is an empty string. Save a copy of the workbook before running a macro; macros cannot be undone with Ctrl+Z.

Sub FindLastColumnOnSheet()
    Dim ws As Worksheet
    Dim lastCell As Range
    Dim lastCol As Long

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

    ' Search formulas and values, moving backward by column.
    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 values or formulas were found."
    Else
        lastCol = lastCell.Column
        MsgBox "Last column number: " & lastCol
    End If
End Sub

The Nothing check matters because Find returns no Range object if it finds no match. When it does find one, lastCell.Column is the numeric column index. This search is useful when the last occupied cell may be anywhere on the sheet, rather than only in a known header row.

How the Search Settings Affect the Result

Excel’s Find settings can be remembered between searches, including searches made through the Find dialog. Specifying the important arguments in VBA makes the result more predictable. In particular, LookIn determines whether Excel searches formula text or displayed values, while SearchOrder and SearchDirection determine how it traverses the grid.

LookIn:=xlFormulas includes cells containing formulas, even if a formula currently displays a blank result. Use xlValues instead when the intended test is based on the displayed result. A formula that returns an empty string may then be treated differently.
SearchOrder:=xlByColumns makes the rightmost matching column the important result. If you used xlByRows, the search would prioritize the last matching row instead.
SearchDirection:=xlPrevious searches backward through the specified order. With the other settings shown, the match is in the rightmost column containing a match.
SearchFormat:=False prevents an old format criterion from affecting the search. Formatting alone is not what this code is looking for.

These settings also clarify the definition of “occupied.” A cell containing a constant or formula counts; a cell that only has a fill color or border does not match the wildcard search. If you want formulas that display nothing to count as used, searching formulas is usually the more suitable choice.

Choose a Method for a Table or Used Range

If the data is an Excel Table, use the table’s own boundaries rather than scanning the whole worksheet. A table has a defined range that moves as the table expands or contracts. For a table named SalesTable on the Data sheet, its rightmost column number can be calculated as the table’s starting column plus its column count minus one.

With a table named SalesTable on the Data worksheet, this macro returns the absolute worksheet column number of the table’s final column. Save a copy of the workbook before running a macro; macros cannot be undone with Ctrl+Z.

Sub FindLastTableColumn()
    Dim ws As Worksheet
    Dim tbl As ListObject
    Dim lastCol As Long

    ' Identify the worksheet and Excel Table.
    Set ws = ThisWorkbook.Worksheets("Data")
    Set tbl = ws.ListObjects("SalesTable")

    ' Convert the table's width into its final sheet column.
    lastCol = tbl.Range.Column + tbl.ListColumns.Count - 1

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

For example, if the table starts in column C and has six columns, the final column number is 8, which is H. This method reports the table boundary, not unrelated cells elsewhere on the worksheet.

UsedRange is another option when you specifically need Excel’s reported used area. Be careful: formatting can extend that area beyond the cells with actual data, so it is not a dependable way to identify the last column containing a value. If you calculate the right edge from a UsedRange object, use its starting column as well as its width; UsedRange.Columns.Count alone is only a width and may not be the absolute worksheet column number.

Common Problems and How to Avoid Them

A frequent error is using End(xlToLeft) on row 1 when the data continues farther right on another row. That code is behaving as written: it answers a row-specific question. If the sheet is irregular, switch to the worksheet-wide Find method or use a row known to contain every relevant field.

The row is completely blank: the End-based expression returns column 1, because its starting point is the far-right cell and the search reaches the beginning of the row. Check the result’s cell value or use a separate test if a blank row must be reported as having no data.
A formula looks blank: a formula such as one returning an empty string can still be present. Choose xlFormulas if the existence of the formula matters, or xlValues if you care about the displayed result.
The reported edge is unexpectedly far right: check whether the code is examining a different worksheet, whether the selected row contains a distant value, or whether you are relying on UsedRange and formatting.
The worksheet name is wrong: a hard-coded name such as Data must match the tab name exactly. If a sheet might be renamed, consider assigning the worksheet another reliable way or handling the missing sheet explicitly.
The macro changes something but Ctrl+Z does not reverse it: Excel’s Undo history does not undo VBA macro actions. Save a separate copy before running macros that edit workbook content.

Also distinguish the active workbook from the workbook containing the macro. The examples use ThisWorkbook, which refers to the workbook where the VBA code is stored. ActiveWorkbook refers to the workbook currently active in Excel and can be a different file. Using an explicit workbook reference helps avoid searching the wrong sheet.

Return a Column Number or Use the Result

Both common methods return a number such as 8, not the letter H. VBA generally works more reliably with column numbers because they can be passed directly into Cells, Columns, loops, and range calculations. For example, if lastCol is 8, ws.Cells(1, lastCol) refers to H1.

A typical next step is to loop through all columns from 1 to the detected edge, or to construct a range between a fixed starting column and lastCol. Remember to check that a match exists before using a Range returned by Find. If you only need the last column for a row and know that the row is not blank, the End-based result can be used directly without a separate worksheet-wide scan.

Frequently Asked Questions

How do I find the last column in a specific row in VBA?
Use ws.Cells(rowNum, ws.Columns.Count).End(xlToLeft).Column. Set rowNum to the row to inspect; the result is a column number. For a blank row, add a check if column 1 should not be treated as a valid result.

How do I find the last column with data anywhere on a sheet?
Use Find with SearchOrder:=xlByColumns and SearchDirection:=xlPrevious, then read the returned cell’s Column property. Check whether the returned Range is Nothing before using it.

Does End(xlToLeft) find formulas that display blank?
End moves through cells based on whether they contain entries, so a formula is still an entry even when its displayed result is an empty string. If you need a result based on displayed values, choose and verify the search behavior appropriate to that requirement.

Why does my last column result seem too large?
The code may be searching the whole worksheet rather than the header row, or the chosen row may have a distant entry. If the method uses UsedRange, formatting beyond the data can also expand the reported boundary.

Can I use this method with an Excel Table?
Yes. For a table, use its Range and ListColumns.Count to calculate the table’s right edge. That returns the last table column, which may differ from the rightmost used column elsewhere on the worksheet.

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.