excelavon

VBA Find Last Column With Data: Reliable VBA Methods

VBA & macros · Updated November 3, 2026

VBA Find Last Column With Data: Reliable VBA Methods

To find the last column containing a value or formula anywhere on a worksheet, use VBA’s Range.Find method with SearchOrder:=xlByColumns and SearchDirection:=xlPrevious. Searching with LookIn:=xlFormulas includes formulas even when they currently display a blank result; the code below returns the column number, or 0 if the sheet has no content.

Use Find to locate the last used column

Find is generally the most dependable choice when the question is “What is the rightmost cell containing content anywhere on this sheet?” It searches the worksheet’s cells rather than relying on a particular data row. Setting the search order to columns and the direction to previous makes the final match the rightmost matching cell.

Here is a reusable function for Excel for Microsoft 365 on Windows. Put it in a standard VBA module, such as Module1. The returned value is a column number: 1 means column A, 26 means Z, and 27 means AA.

Public Function LastDataColumn(ByVal ws As Worksheet) As Long
    Dim found As Range

    ' Return 0 if no cell contains a value or formula.
    If ws Is Nothing Then Exit Function

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

    If Not found Is Nothing Then
        LastDataColumn = found.Column
    End If
End Function

The After argument identifies the cell from which Excel begins searching. The search wraps around the range, so specifying A1 does not prevent the method from finding content in A1 itself. If no match exists, Find returns Nothing; the function’s default Long value remains 0.

Run the function from a macro

A small calling macro shows how to use the result. Change “Data” to the name of the worksheet you want to inspect. This example reports the number in a message box, but the same value can be used in loops, range references, or export procedures.

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

    Set ws = ThisWorkbook.Worksheets("Data")
    lastCol = LastDataColumn(ws)

    If lastCol = 0 Then
        MsgBox "The worksheet has no values or formulas."
    Else
        MsgBox "Last data column: " & lastCol
    End If
End Sub

Before running a macro, save a copy of the workbook. VBA macros cannot be undone with Ctrl+Z. On Windows, open the Visual Basic Editor with Alt+F11, insert a standard module using Insert > Module, and paste in the code. If the Developer tab is available, Developer > Visual Basic opens the editor as well. On Mac, use Developer > Visual Basic; the keyboard shortcut can depend on the keyboard and function-key settings, so Option+F11 or Fn+Option+F11 may be needed.

What the Find settings mean

Excel’s Find dialog remembers some settings from previous searches, and VBA’s Find method can be affected by those saved options when they are not supplied explicitly. The example spells out the important arguments so its behavior is less dependent on what someone last selected in Find and Replace.

  • What:="*" searches for a match to any text. In Excel’s Find method, the asterisk is a wildcard, so it can match cell content of different kinds.
  • LookIn:=xlFormulas searches formula contents as well as constants. A formula that returns an empty string is still a formula and can therefore count as content.
  • LookAt:=xlPart allows the wildcard to match within cell content rather than requiring a whole-cell match.
  • SearchOrder:=xlByColumns searches by column, while SearchDirection:=xlPrevious moves backward through that order to find the last match.
  • MatchCase:=False makes the search case-insensitive, which is immaterial for the wildcard but keeps the setting explicit.
  • SearchFormat:=False prevents an old formatting criterion from restricting the search.

These options describe a content search, not a formatting search. A cell that only has a fill color or border, with no value or formula, is not a match in this example.

Find the last column in a particular row

Sometimes “last column” means the last populated cell in a known header row or record, not the furthest content anywhere on the worksheet. In that case, restrict the search to that row. The following function returns 0 if the row is empty or if the supplied row number is outside the worksheet’s valid range.

Public Function LastDataColumnInRow(ByVal ws As Worksheet, _
                                    ByVal rowNum As Long) As Long
    Dim found As Range

    ' Reject a missing sheet or an invalid row number.
    If ws Is Nothing Then Exit Function
    If rowNum < 1 Or rowNum > ws.Rows.Count Then Exit Function

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

    If Not found Is Nothing Then
        LastDataColumnInRow = found.Column
    End If
End Function

For example, if row 1 contains headings in A1, B1, and F1, this function called with rowNum equal to 1 returns 6. The worksheet-wide function also returns 6 if F1 is the rightmost content on the sheet, but it could return a larger column if another row has content farther right. That distinction matters when building a table from a header row: decide whether the boundary should come from the headers, a data row, or the entire sheet before choosing a method.

Choose between Find, End, and UsedRange

There is no single “last column” definition for every task. These approaches answer related but different questions, so choose based on what counts as data and where it is expected.

  • Find: Best for locating the rightmost cell with a value or formula across a whole sheet or a defined range. It returns a matching cell, so its row and column are both available.
  • End(xlToLeft): Useful for moving left from the far-right edge of a specific row, similar to pressing Ctrl+Left in Excel. It is concise when the row is known to contain the relevant data.
  • UsedRange: Useful for inspecting Excel’s recorded used area, but its boundary can include cells that were formatted or edited previously. It does not necessarily equal the last column with current content.

For instance, this expression can find the rightmost nonempty cell in row 1: Cells(1, Columns.Count).End(xlToLeft).Column. It is convenient, but on a completely empty row it returns 1, which can be mistaken for column A containing data. It also does not answer the worksheet-wide question if another row extends farther right. Use a content check or Find when empty rows or the precise definition of data matter.

UsedRange is often misunderstood as a guaranteed content boundary. Formatting an otherwise empty cell far to the right can make the reported used area extend there. Excel may adjust its recorded area over time, but relying on that behavior as a substitute for searching content can produce an incorrect last-data-column result.

Formulas, blanks, and formatting

The choice between xlFormulas and xlValues depends on what your procedure should treat as present. With xlFormulas, a cell containing =IF(A1="","",A1) is found even when its displayed result is an empty string. This is often right when formulas define the structure of a report or when a later calculation may produce a visible result.

If you care only about displayed values, consider searching with LookIn:=xlValues instead. A formula currently displaying an empty string may not be meaningful for that task. Test this behavior against the actual data and Excel version used for your workbook, especially when formulas, errors, and blanks are mixed. A cell that looks blank can contain a space, an apostrophe, or a formula; those are not necessarily equivalent to a genuinely empty cell.

Neither search option treats formatting alone as content when the search is set up as above. If your goal is to find the rightmost formatted cell, that is a different problem and needs a formatting-aware approach. For ordinary data processing, including formatting-only cells can be undesirable because it may cause loops to scan many unnecessary columns.

Common mistakes and how to avoid them

  • Leaving Find options out: A search that relies on remembered settings may behave differently after a user changes the Find dialog. Explicitly set the search arguments, including LookIn, SearchOrder, SearchDirection, and SearchFormat.
  • Using a row method for a whole sheet: End(xlToLeft) examines one row only. If rows have different widths, it can miss content elsewhere.
  • Assuming 1 proves column A has data: End-based navigation can return the first column for an empty row. Check for content or use the Find pattern that can return 0.
  • Confusing column number with column letter: VBA’s Column property returns a number. Column 28 is AB; it is not the text “AB.” Convert it to a letter only if a display or another procedure requires one.
  • Searching the wrong worksheet: Unqualified references such as Cells may refer to the active sheet. Passing a worksheet object and using ws.Cells makes the target explicit.
  • Counting formatting as data: A wide UsedRange or a Ctrl+End result can reflect past formatting, not current content. Use a content search for a content boundary.

When the workbook has thousands of rows and many formulas, keep the search limited to the relevant worksheet or range rather than repeatedly searching every sheet inside a loop. Find is usually simpler than scanning every cell individually, but the best optimization is to avoid searching a larger area than the task needs.

Frequently Asked Questions

How do I get the last column letter instead of its number?
The Find result’s Column property is numeric. To get a letter for a valid column number held in lastCol, use Split(Cells(1, lastCol).Address(False, False), "1")(0) in a context where Cells is qualified to the intended worksheet. For example, use ws.Cells rather than an unqualified reference inside a multi-sheet macro.

Does the macro count a formula that displays nothing?
Yes, the supplied function uses LookIn:=xlFormulas, so a formula returning an empty string still counts as content. Use a values-based search if the task is specifically about what is currently displayed, and verify the result for the formulas in your workbook.

Why does my last-column result seem too far to the right?
Check whether another row has a value or formula farther right than the row you were looking at. Also check whether the code is using UsedRange or Ctrl+End, which can include formatting or prior edits. A worksheet-wide Find search and a row-restricted search answer different questions.

What does the function return when the sheet is empty?
The function returns 0 when Find returns no match. Check for 0 before using the result to build a range, since column 0 is not a valid worksheet column.

Can I use this code in Excel for the web?
Excel for the web can open workbooks containing VBA, but it cannot create, run, or edit VBA macros. Use the desktop Excel application for Windows or Mac to run the code; the Windows-specific keyboard shortcut and ribbon guidance above applies to Windows.

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.