VBA Find Last Used Column in Excel: Reliable Methods

To find the last used column anywhere on an Excel worksheet with VBA, use Range.Find with SearchOrder:=xlByColumns and SearchDirection:=xlPrevious. The example below returns the column number, or 0 if the sheet has no content:

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

    Set ws = ThisWorkbook.Worksheets("Data")

    With ws.Cells
        Set lastCell = .Find(What:="*", After:=.Cells(1, 1), _
            LookIn:=xlFormulas, LookAt:=xlPart, _
            SearchOrder:=xlByColumns, SearchDirection:=xlPrevious, _
            MatchCase:=False, SearchFormat:=False)
    End With

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

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

Replace Data with the worksheet name. A number such as 5 means column E; use ws.Cells(1, lastCol).Address if you need a cell address. Save a copy of the workbook before running a macro: VBA changes cannot be undone with Ctrl+Z.

What “last used column” means

There is no single definition that fits every workbook. You might mean the rightmost cell with a value or formula anywhere on the sheet, the final header in one row, or the last column in a particular table. Choose the definition before choosing the code, because each method examines a different scope.

The worksheet-wide Find method looks across the worksheet. In a sheet where data appears in A1:D20 and also in J3, it reports column J, even if the main table ends in D. If you only want the last populated header in row 1, a row-based method is more appropriate. That distinction prevents a distant note, helper formula, or second data block from unexpectedly changing the result.

In current desktop Excel, a worksheet has 16,384 columns, ending at XFD. VBA’s Column property returns a number, not a letter. A column number is usually more useful in code because it can be passed directly to Cells(row, column).

How the Find method works

Find searches a range for content. Here, ws.Cells makes that range the whole worksheet, and What:="*" searches for a match. Setting SearchOrder:=xlByColumns makes the search traverse columns; SearchDirection:=xlPrevious moves backward through that order, so the match returned is in the rightmost column containing a match.

The other arguments make the search more predictable. LookAt:=xlPart allows a partial match, MatchCase:=False ignores letter case, and SearchFormat:=False prevents a saved Find-format setting from affecting the result. After:=.Cells(1, 1) gives Excel a starting cell; Find wraps around the range, so this starting point does not prevent it from locating content in column A.

Find settings such as what to look in and whether to match whole cells can persist between VBA calls and the Excel Find dialog. Providing the important arguments explicitly avoids relying on a user’s previous search settings. The result is a Range object, so the macro checks Is Nothing before reading its column. An empty worksheet has no match, and its last-column result is set to 0 rather than incorrectly treating column A as occupied.

Choose formulas or displayed values

The example uses LookIn:=xlFormulas. This treats formulas as content even when a formula displays an empty string, as in a cell containing =IF(A1="","",A1). That is often desirable when “used” means that a formula has been entered and should be included in the result.

If the question is instead “What is the rightmost column with a displayed value?”, change the argument to LookIn:=xlValues. A formula whose calculated result is blank may then be excluded. Be clear about which definition the rest of your procedure needs: a cell can contain a formula without showing visible text or a number. Formatting alone is a different issue; Find is generally used to locate cell content, not merely cells that have formatting applied.

For example, suppose a worksheet has values in A1 and D1, plus a formula in G1 that returns an empty string. With the formula-search approach, the result is column 7 (G). A values-based search may report column 4 (D), depending on the formula’s evaluated result. This difference is not a bug; it reflects the different search criteria.

Find the last column in one row

When a task concerns a specific row—often a header row—search only that row rather than the entire sheet. This short pattern checks row 1 and returns 0 if it is empty:

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

    Set ws = ThisWorkbook.Worksheets("Data")
    headerRow = 1

    If Application.CountA(ws.Rows(headerRow)) = 0 Then
        lastCol = 0
    Else
        lastCol = ws.Cells(headerRow, ws.Columns.Count) _
                    .End(xlToLeft).Column
    End If

    MsgBox "Last header column: " & lastCol
End Sub

End(xlToLeft) acts like moving left from the far-right cell in that row to the next nonempty cell. It is compact and fast for a row with headers or other content. The empty-row check matters: without it, a completely empty row can produce column 1, which could be mistaken for a real result. CountA counts formulas even when they display an empty string, so this check follows a formula-presence definition of occupied.

For a particular data row, set headerRow to that row number. Do not use this method to find the rightmost content anywhere on the worksheet: content in another row is outside its scope.

Pick a method for the data structure

Any content anywhere on the sheet: use the worksheet-wide Find example. It handles scattered content better than checking only one row.
Headers or entries in one known row: use End(xlToLeft) with an empty-row check.
A formatted range or previously used area: UsedRange may be useful for inspecting Excel’s recorded used area, but it is not a reliable substitute for “rightmost cell containing a value or formula.” Formatting or past edits can make its bounds larger than the live data.
A structured Excel table: use the table’s ListObject range when the question is about the table, not every cell on the worksheet. A table can have a defined width even when some cells are blank.

For a table named SalesTable, ListObjects("SalesTable").Range.Columns.Count gives the table’s width, not necessarily the worksheet column number of its right edge. To get that worksheet column number, add the table’s starting column and subtract one. If the table starts in column C and is five columns wide, its right edge is column 7 (G). This is a useful distinction when code needs to address cells elsewhere on the sheet.

Common problems and how to avoid them

Getting 1 on an empty row: check for an empty row before calling End(xlToLeft), as shown above.
Finding a column farther right than expected: worksheet-wide search includes stray values, formulas, and other blocks. Restrict the search to a row, table, or known range if that is the intended scope.
Counting a formula that looks blank: use xlFormulas when formula presence matters; choose a values-based search if calculated display values are what matter.
Getting an error on an empty sheet: Find returns no range when it finds no match. Test lastCell Is Nothing before accessing lastCell.Column.
Using an unqualified range: Cells and Range without a worksheet qualifier can refer to the active sheet. Qualify them with ws so the macro checks the intended worksheet even if another sheet is active.

Also consider hidden rows and columns. Hiding a column does not remove its contents, so a worksheet-wide Find can still return a hidden rightmost column. If you want the last visible column, that is an additional condition; the basic “last used” search does not filter by visibility.

Use the result in other VBA code

Once you have lastCol, use it to build a range or loop over the populated columns. For example, with ws set to the intended worksheet and lastCol holding the result, ws.Cells(1, lastCol) refers to the cell in row 1 of that column. Check that lastCol > 0 before using it on an empty sheet.

A column number can be converted to a letter when a human-readable label is needed, but keeping the number for range operations avoids unnecessary conversion. For instance, a loop from 1 through lastCol can inspect each column with ws.Cells(rowNumber, columnNumber). If a worksheet contains several independent data blocks, calculate a last column for each relevant range rather than assuming one sheet-wide boundary applies to all of them.

When adding code to a workbook, open the Visual Basic Editor with Alt+F11 on Windows, insert a standard module through Insert > Module, and paste the procedure there. On Mac, the Visual Basic Editor is available through Tools > Macro > Visual Basic Editor in Excel, though keyboard shortcuts can vary with the keyboard and macOS settings. Save a workbook containing VBA as an Excel Macro-Enabled Workbook (.xlsm) if you need to keep the macro.

Frequently Asked Questions

What is the simplest VBA code to find the last used column?
Use Find on the worksheet’s cells with SearchOrder:=xlByColumns and SearchDirection:=xlPrevious. Check whether the returned range is Nothing before reading its Column property.

How do I get the last used column as a letter?
The Find result’s Column property is numeric. To obtain the cell address, use lastCell.Address; to identify a particular row in the result column, use ws.Cells(1, lastCol).Address. Excel will include the column letters in the address.

Does UsedRange always show the true last column with data?
No. Its bounds can include cells affected by formatting or earlier worksheet use, so it can extend beyond the current data. Use Find for a content-based worksheet-wide answer, or a row-specific method when only one row matters.

How can I find the last column in row 1 only?
Check whether row 1 is empty, then use ws.Cells(1, ws.Columns.Count).End(xlToLeft).Column. This searches that row, not the whole worksheet.

Why does my macro return a column containing a blank-looking cell?
The cell may contain a formula that returns an empty string. A search using LookIn:=xlFormulas counts the formula as content even when the result looks blank. Choose a values-based definition if you want to disregard formulas whose displayed result is empty.

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.